Report refresh issue

[MCG]

Hi,

I am trying to refresh BI report thru Power Bi service but unfirtunatelly one issue occured:(

My report is based on SQL selectection  and Excel file saved on Sharepoint location

SQL select and Excel querries are refreshing with no problems if are run separately - I assume that all the necessary gateways are ok

Problem occurs if I merge my SQL selection results with Excel in Power Query - it is needed to do some raw dar transformations

If i try to refresh report with merged SQL and Excel it does not work:(

I would appreciate any hints as PQ transformations are something obvious and therefore should not influence report refresh...

Thanks

MCG

[arvindsingh802]

Please refer Solved: Re: does SharePoint Online use gateway even when n... - Microsoft Power BI Community

If this post helps, then please consider Accept it as the solution and give it a thumbs up

[arvindsingh802 · Accepted solution]

when you merge data from On-Prem and SharePoint online the merge operation happen on your Gateway only thus you have to enable "Allow user's cloud data sources to refresh through this gateway cluster" option in Gateway setting. (Merge or append on-premises and cloud data sources - Power BI | Microsoft Docs)

If you want to SharePoint process not to run on gateway follow below steps

1. Create dataflow for On premise data using gateway connection

2. In your dataset replace onpremise source with Dataflow

Now your merge opration will be perfoemed online and you will not be requing to enable "Allow user's cloud data sources to refresh through this gateway cluster" option anymore.

Solved: Re: does SharePoint Online use gateway even when n... - Microsoft Power BI Community

If this post helps, then please consider Accept it as the solution and give it a thumbs up