Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
webportal
Impactful Individual

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.

bdarbo78
Advocate I

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.

MichaelDoig
Advocate I

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... 

michaelsh
Kudo Kingpin

Same here

Simple query from PostgreSQL with 2000 rows takes 7 minutes to validate.

Natively it runs 0.83 seconds...

datahamster
Regular Visitor

Same here. What's wrong with that dataflows validationing? 😞

PaulKn
Advocate II

I have also recently started experiencing long delays with the "Validating queries We are validating the output schemas for your queries to ensure they can be loaded..." message when trying to save a dataflow. The source is SQL Server but it isn't running any queries against the database whilst this painfully long wait occurs. I'm not performing any transformations in Power Query, I just have a number of entities sourced directly from SQL views.

Anonymous
Not applicable

Adding to this conversation with our experience. We have Premium and it has only been in the past 2 days that we started seeing significant slowdown.  We edit dataflows often and 5 days ago I edited dataflow A and it took 10 min to validate, today, same dataflow A i'm on 30 min so far and still stuck in this state of "Validating queries". @adams

Eltoro
New Member

Experiencing the same delays here, I was very pro data flows, but this has me ready to jump ship.  Hoping there is a fix soon.

PhilPrentiss
Regular Visitor

Same experience, Dataflow usage has become untenable.  This really belongs as an incident because I'm certain this in not what Microsoft would sell as normal system behavior.  I'll create a ticket and see if we can get a resolution.

michaelsh
Kudo Kingpin

So Slooooooowwwwwww!!!