Forum Discussion
• Data Flow - SharePoint Folder Connector REALLY SLOW - Evaluation Cancelled
Hi Anonymous ,
There are many causes lead to slow performance. You are using flat files as datasource, so there isn't query folding in your datasource. The mashup engine needs to load all files at the same time then do transitions, it will lead to large memory use . The complex transition will leads to large cpu use.
Are you using dataflow in premium? You can try to enable Enhanced Dataflows Compute Engine and enlarge the Max Memory in workload. Please refer to https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-premium-workload-configuration
And use computed entity is also a option for you.
Here is a official document for your reference: https://docs.microsoft.com/en-us/power-query/optimize-expanding-table-columns . The expand steps could be the issue of slow performance. when you use join and expand steps, it will increases the number of HTTP calls by one for each row in your datasource.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hello my friend. Thanks for the reply. Yes, this code was actually created in PBI Desktop... it was really slow to load all the data ( it used to take 1-2 hours to be able to load in the report), but it worked in PBI Desktop...my idea was to move to data flow to optimize the query performance and be able load the report faster.
I just tried to update the query online with a new folder with only 2-3 files...but it's still very very slow 😞
it does not makes sense....there is like, 80MB in the folder.
Hi Anonymous ,
There are many causes lead to slow performance. You are using flat files as datasource, so there isn't query folding in your datasource. The mashup engine needs to load all files at the same time then do transitions, it will lead to large memory use . The complex transition will leads to large cpu use.
Are you using dataflow in premium? You can try to enable Enhanced Dataflows Compute Engine and enlarge the Max Memory in workload. Please refer to https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-premium-workload-configuration
And use computed entity is also a option for you.
Here is a official document for your reference: https://docs.microsoft.com/en-us/power-query/optimize-expanding-table-columns . The expand steps could be the issue of slow performance. when you use join and expand steps, it will increases the number of HTTP calls by one for each row in your datasource.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hello v-deddai1-msft and selimovd , thanks for the reply and support. I found a way to optimize my query, the SharePoint that was hosting my folders and files, was too ''busy'', it was hosting a lot of folders and files...It was taking forever to find the folder to use as dasource. So I created a separate Site to use as repository for flat files, now te performance is better...not super fast, but I can manage. I'll read that documentation regarding expanding and merging queries.
Thanks again both of you for the support!!!
Source table size < 300K rows, accessing data via an on-prem Gateway, from Power BI Desktop Aprl & now May version.
I have a similar situation but it is even worse as the Mashup Engine threads water forever in the last step of a reference chain of intermediate queries with Enable Load = OFF. This is what it looks like:
query_0 --> query_1 --> query_2 --> query_3 --> final_query
A few days ago, with the same M code, all was working and being processed at an acceptable speed. But after a Save As and a machine reboot (using the April version), suddenly query_2 and query_3 would take the 3+ hours to complete! Same happens after updating Power BI to the May version.
I log in today, see that finally query_0, query_1, query_2, query_3 have all completed but there still is a question mark next to final_query. I click on final_query and it goes on and on forever. And the code of the final query is this simple:
let Source = query_3 in SourceIt is just a reference to the previous query, which has completed. And when looking at the Task Manager, the Mashup Engine is showing Very high power usage using > 11% CPU and 2GB of RAM!! What on Earth is it doing? All the code says is pull the data from the previous query into a table and bloody load it into the model! Why is it stalling when all previous queries have completed successfully?
