Forum Discussion
Filter rows in query editor - refresh slow
The problem has been solved in Power Bi desktop, since the DBA didn't want a extra view in the DB.
In stead of doing the filtering in Power Query, I did the following.
Created a new data source Power Bi Desktop
Enter Database Names and ServerSelected advanced mode.
wrote the select statement.
I did not time it by a stop watch, but I think data refresh time went from 30 min to 30 sec.
I found the inspiration to the fix here: https://www.youtube.com/watch?v=EhGF372t0sU
Hi. You have to consider that power bi won't add additional load time unless you have a lot of crazy power query steps. Consider running a SELECT * FROM table WHERE IntervalType = 8 in the engine with Management Studio or Data Studio. Then that should be the time that will take the power bi service to refresh directly without a gateway.
About Power Bi Desktop you are right, it will download all the table compressed. That is why it can take a bit longer than the time of the query result directly on the engine.
The important thing is that once you have this published on service you don't need a gateway and it should take almost the same time of the query against the engine.
Hope this makes it clearer.
The problem has been solved in Power Bi desktop, since the DBA didn't want a extra view in the DB.
In stead of doing the filtering in Power Query, I did the following.
Created a new data source Power Bi Desktop
Enter Database Names and ServerSelected advanced mode.
wrote the select statement.
I did not time it by a stop watch, but I think data refresh time went from 30 min to 30 sec.
I found the inspiration to the fix here: https://www.youtube.com/watch?v=EhGF372t0sU