cancel
Showing results for 
Search instead for 
Did you mean: 

PowerQuery - Validating queries when saving is taking excessively long time

I'm struggling to be able to work efficiently with Dataflows as I am experiencing excessive times to 'validate' queries after pressing the 'Save & Close' button. For entities with even a minimal amount of transformation (eg. simply changing some column data types) I've experienced wait times of over 3 hours to simply save the query to exit out back to the main entity menu/view. Should things be taking this long for an output schema validation process, regardless of the size of the underlying table data? Is this an issue being experienced by others as well?

This is making it simply impossible for me to work with the Dataflows product in this way.

 

2019-12-03_16-00-57.png

Status: New
Comments
Post Prodigy

In my experience, data flows take an enormous amount of time just to validate queries.

In some cases, they take hours, when in Power BI Desktop (with the exact same code), just a few seconds, so I guess it is a matter of capacity.

Regular Visitor

I had some sucess with the use of Table.View, an idea suggested by Chris Webb here.
It is a fairly complex technique - at least to me - that is used as a last step to sort of fool the UI. Read the article. But I had to go further: for the OnTake function, which is used when the system tries to read a limited number of rows from the table, I created a dummy table with the Enter data feature and a copy/paste of a few rows from the real table. Then I set the OnTake function to return this dummy table regardless of the rowcount requested. This way, when a preview of the data is requested - I believe this is what the validation step does - the data is provided immediately. Only the actual refresh reads the real table. 

 

Though this can help, I do think we should have to go through all of this.

Frequent Visitor

As a recent convert to Data Flows, I was thrilled at the capabilities and the ease of exporting M Query Code into the Data Flow.

 

And then simple Entity Additions would take an hour or more, or time out; to validate. 

 

This is diabolical...