Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Merge Excel and SQL error refresh

  • 1.  Merge Excel and SQL error refresh

    Posted Nov 13, 2019 08:47 AM

    Hi, 

    I have an issue with the refresh on the service. 

    I have this issue when I try to merge data from Excel (Sharepoint) and Sql. 

    If I try to delete the merge and refresh the simple source, it's good. When I use merge in Edit Query, it refresh normaly in desktop and it give me error when I try to refresh from Service. The problem is the different gateway ? 



    ------------------------------
    domenico moriello
    bim manager
    3934983521
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Merge Excel and SQL error refresh

    Top Contributor
    Posted Nov 13, 2019 08:59 PM
    Hi !

    Please elaborate what you mean here by different gateway, both SQL & Share Point are different servers ?

    If you are using Share Point (Online) i don't think this will be a problem.

    You need to check in PBI Service that gateway credentials are correct & also when you publish PBI Desktop credentials are provided as well.

    Regards,


    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Merge Excel and SQL error refresh

    Posted Nov 14, 2019 08:25 AM
    I think that the problem is about the different gateway but I am not sure.
    I have 2 source, Excel file in the sharepoint and a SQL db.
    I have merged the in the Excel file, the SQL data. It is good in the Power BI desktop.
    When I try to refresh the report in Power BI service it gives error.

    I have tried to delete the merge step in the Edit query, publish the report again and I don't have the issue.
    It comes when I try to merge the table in Edit query.

    ------------------------------
    domenico moriello
    bim manager
    3934983521
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Merge Excel and SQL error refresh

    Top Contributor
    Posted Nov 14, 2019 12:48 PM
    Hi !

    So it seems more of a data issue than gateway, because if its related to gateway it won't work with or without merge step in the PBI Service.

    There might be some casting issue which are causing the issue. Try limiting the rows in excel & SQL, may be 10 rows from each & perform the merge to see if this related to data or gateway.

    Please confirm you are using Share Point (Online) not Share Point (On-Prem)

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Merge Excel and SQL error refresh

    Posted Nov 15, 2019 04:02 AM
    Hello

    I've had a similar issue in the past, merging Excel (on SharePoint) and SQL (on-prem) worked fine in desktop but when in the service I received an error message and unable to configure refreshes through the gateway.

    I had to make a small change to the settings on the Power BI gateway and select the option in the manage gateway settings 'Allow users cloud data resources to be refreshed through this gateway'  I've attached a screen print to illustrate this.  Hope this resolves your problem as I also found this issue frustrating and it took a while to find the root cause.

    Thanks  Jo
    Gateway settings



    ------------------------------
    Jo Price
    Data Analyst
    Godiva Consultants LTD
    coventry
    07723591037
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Merge Excel and SQL error refresh

    Posted Nov 15, 2019 04:45 AM
    issueHi Jo,
    I am using a gateway from my company.
    I am not able to set this setting.
    Where can I select this setting ?


    ------------------------------
    domenico moriello
    bim manager
    3934983521
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Merge Excel and SQL error refresh

    Posted Nov 15, 2019 05:09 AM
    Hi

    From where you are now click on the cog next to actions, this will take you to the gateway setup and the screen I previously mentioned.  I do believe you need to be a gateway admin to be able to select the check box for using cloud data through gateway.

    Hope this helps

    Jo

    ------------------------------
    Jo Price
    Data Analyst
    Godiva Consultants LTD
    coventry
    07723591037
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Merge Excel and SQL error refresh

    Posted Nov 15, 2019 09:39 AM
    Edited by domenico moriello Nov 15, 2019 09:39 AM
    I have tried but I have always this issue.

    ------------------------------
    domenico moriello
    bim manager
    3934983521
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Merge Excel and SQL error refresh

    Top Contributor
    Posted Nov 15, 2019 03:23 PM
    Hi Dom !

    From Power BI Service go to Gateway & verify both Datasets connection are working fine there, you might need to check it from there as well.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Academy - Online Interactive Learning from Experts