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 30 days ago

    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
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Merge Excel and SQL error refresh

    Top Contributor
    Posted 30 days ago
    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
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Merge Excel and SQL error refresh

    Posted 29 days ago
    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
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Merge Excel and SQL error refresh

    Top Contributor
    Posted 29 days ago
    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
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Merge Excel and SQL error refresh

    Posted 28 days ago
    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
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Merge Excel and SQL error refresh

    Posted 28 days ago
    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
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Merge Excel and SQL error refresh

    Posted 28 days ago
    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
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Merge Excel and SQL error refresh

    Posted 28 days ago
    Edited by domenico moriello 28 days ago
    I have tried but I have always this issue.

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

    Conference-PBI_200x200


  • 9.  RE: Merge Excel and SQL error refresh

    Top Contributor
    Posted 28 days ago
    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
    ------------------------------

    Conference-PBI_200x200