Edit queries preview must reload each time a change is made

[billmanh]

Each time a change is made to the data preparation process,  the application must re query the origional datasource. This is very slow. The whole point of pulling a local subset for processing is so that you can make several changes locally without querying the server  each  time. There should, at the very least, be an option to stop querying unitl you are finnished making changes so that you can quickly make changes.  Depending on the query it can take hours to complete tasks like rename columns or filter when you don't need to fetch new preview data at each  incremental change.

[v-qiuyu-msft]

Hi @billmanh,

For your requirement, if are you are very familiar with the Power Query, you can open Query Editor -> Advanced Editor write Power Query corresponding to the Applied Steps.

Meanwhile, you can submit an idea in Ideas Forum.

Best Regards,

QiuyunYu

[billmanh]

Thanks for responding, but this is not delivered.

I think you totally fail to understand the issue. Of course you can open the advanced query editor and hack out the entire solution by yourself, but you can't see the result untill you are finished. You also can't see the results of each step. So if you already know everything you can just type it out and you are done. This is a solution to a different probelm. 

The probelm I stated above was that you can't use the GUID to make step changes without having to query the data each time. You will want to make incremental steps and see how this changes the data. This is a regular step in data exploration. However this is tedious and slow when you have to refresh the preview each time a small change is made.

It is unfortunate that you are choosing to ignore this issue.

[billmanh]

Thanks but this is not delivered, you should have chaned this to 'ignored'.

Of course you can open the advanced editor and type out the entire solution in one step. This assumes that you have an intimate understanding of the data as well as an understanding of the shape of the entire data. The whole point of data exporation is that you can make changes step by step and see how the dataset is affected. However this is very tedious in Power BI as you have to wait for the data preview to refresh each time you make a small change.

Rather than forcing the user to hack out the entire solution with scratch, or wait through a tedious refresh process, you should make the incremental changes to a static set and allow the user to refresh data when they need it. This would allow the user to quickly make incremental changes and would greatly speed up the process.

[v-qiuyu-msft]

Hi @billmanh,

Maybe my previous post make you have some confusion. I mean the guy who are familiar with the Power Query can write whole backend Power Query without based on each applied step in Query Editor. And I already sent an email to consult if there is any other workaround internally. Will update to you once get feedback.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @billmanh,

I got the feedback below:

"There is nothing obvious (like a setting) to change this behavior.

I would suggest to try a Reference table. For example if the source table is named Data, create a reference to it named DataSteps with the following query:

let
    Source = Data
in
    Source

When the DataSteps table is finished with all steps, then copy/paste the steps to the Data table."

Best Regards,
Qiuyun Yu

[dkay84_PowerBI]

Would using Table.Buffer be usefull in this instance?  I'm still unclear about how/when to use that and the result on performance.

[SamNing]

I am having the similar issue.

The issue has two impacts

1. At the time of writing the query, each steps is slow, maybe hacking the process would solve it as suggestions above.

2. Even after designing the process, a simple refreshing would take ages.

To me the issue is that Power Query is pulling data at each steps. If I have a subsequent query referring to the prevous query, not only the originaly Query is pulling data from source, but the subsequent Quary is pulling data from the source too. Even so, each extra steps seem to be pulling data from the source. This makes desigining and testing of complex Power Query almost impossible. In quantity terms, if the original data was 1M, up to step 2, the data pulling would be 2M, step 3 would be 3M....etc.

In an ideal world, the data should be pulled only once at the original source. All the subsequent steps/quaries should be working on data of the last step, other than repulling data again. Even if there would be 100 steps, for example, we should only pulling 1M data from the source.

I am still learning, however, this seems to be a big obstacle. Or maybe I have done something wrong?

[Anonymous]

I am facing the same issue. Is there a way to make edits to the data model without causing it to pull the data from server everytime?

[hollasa]

An example of how this might work is currently found in Excel, when building a PivotTable. When you have the PivotTable Fields area open on the right, there's an option at the bottom to "Defer Layout Update". When checked, in order ot udpate the PivotTable, you need to click on the Refresh button. 

I'm currently waiting for a tablel to refresh, after removing an intermediate step, and registered just to request this. Here's hoping that will end sometime in the near future!