In my current scenario, I am dealing with Customer Feedback , Customer Cases data, and several; other realted tables.
All the data comes from dataflow, which just pulls raw data from SQL.
PowerBI Desktop Connects to these dataflows and filters down as required.
even after reducing the amount that is being pulled into dataflow, Just 3 weeks of data per table returns several GB of DATA being refreshed everytime powerquery pulls data from dataflow.
Any further filters applied in powerBI desktop are no good, as query folding does not happen at all.
The Problem
The problem is that with no query folding happening, every time i have to move between steps of a single query, it refreshes the entire 2 or 3 or 5 gb of data from dataflow and then loads the result of that step.
This is a huge problem, as its just increasing the development time of the report.
reducing the amount of dataany less than what i am alreaddy pulling is not an option.
Question
Is there a way to stop Power Query to refresh every time on every step ? unless I can manually hit refresh for that query or step?
Any documentation / reading material/ blog / resource in this direction will be a good help too. I just havent found anything in my research yet.
Well, it seems someone has some work to do on their very hungry dataflows. I see a new cozy relationship with DAX Studio and Vertipaq Analyzer in someone’s future.
I have not looked deeper into the semantics of how the following would apply to your case, so please go with me on this one for the moment. I am taking from the size you mentioned on refresh that this is Premium.
Sometime back, Nikola put out the idea of Hot and Cold tables. This really caught my attention, though I have not had a chance to test it out. However, considering the source, I take his guidance as gospel.
Nikola presented an ultimate guide to Hybrid Tables, and then the one that caught my attention was Hybrid Tables for Poor People. ( I still brown bag a lunch most days )
Links to get you started:
Article: Power BI - Hybrid Tables for Poor People Spoiler: "Look, Nikola, we’ve already heard about the hybrid tables, and we think they are super cool…But, there is a “small” issue…We don’t have Premium”
Thank @ystroman and @Keith you for sharing your inputs there.
Yeah @ystroman , My eyes are sore looking at powerquery loading data for past 5 days
So, @keith, I am not sure of other AI tools, but i looked up on Chat GPT, and guess what . It looks like its making things up and pointing me to click icons that dont even exist in power query.
here is what it says. and i dont have any such options in PQ
Here's how you can do it:
Open your Power Query editor in Power BI Desktop.
On the right side of the Power Query editor, you'll see a pane with Applied Steps. It shows all the transformations applied to the data.
Click on the gear icon (settings) next to each step that you want to disable from loading automatically.
In the "Options" dialog box that appears, uncheck the "Enable Load" option.
Click "OK" to save the changes.
Strangely, this issue doesnt seem to have been in discussions or written about anywhere. or May be I am yet to make the discovery.
Most of the links that you have shared here lead to speeding up of the report / model which does not quite resolve my issue at hand .
@Keith I was excited to implement the chagnes as perthe link you shared, but unfortunately that didnt help
Looks like I have to live with this for now and cntinue my quest for a correct solution
Table.Buffer() function seems to have worked the trick for me to some extent and I believe this is the only way to handle this type of a scenario.
for anyone stumbling on this post in future, it will be a good idea to read the documentation on the function as it may not be the best solution always.
I see a new cozy relationship with DAX Studio and Vertipaq Analyzer in someone’s future.
