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
thecoolnine9
Regular Visitor

For me the 'Validating queries' never completes....

 

I've tried on several dataflows now, some left to run over night and the validation step never completes...

 

I'm used to 3 - 4 hour waits usually, but this week its just plain broken....

altbra
Frequent Visitor

Got a responce from Microsoft regarding this issue. They are actively investigating the issue and this is a priority for them at the moment. Fingers crossed, coleagues. 🙂

Gulan70
Frequent Visitor

What is happening today: validation which took hours last week are now minutes?

 

Edit: but again: this afternoon it is again super slow, hours..., so I was lucky this morning.

altbra
Frequent Visitor

It is dead this afternoon. Same here.

graemejohnson
Helper II

Slow for me too - it's not usable.  Please Microsoft, pull your finger out and get this resolved!

MunroA7
Advocate III

3.5 hours to validate and still going.  I hope someone takes notice and does something as we can't update any reports.

tuken
Frequent Visitor

Same issue here. It always takes a long time to finish validation, but it seems to be particularly slow today.

Gulan70
Frequent Visitor

I stopped validation after 5 hours. will try again later.  It seems hopeless.

bdarbo78
Frequent Visitor

I know it's a pain to implement, but using Table.View seems to help a lot. In my experience, implementing the OnTake handler was key. When the data source does not support query folding, validating the dataflow seems to entail loading the entire table, which can explain the long waiting time. With an OnTake handler that quickly returns a limited number of rows - by using filters like 'retrieve only the last n days of data' instead of the last n years for the GetRows handler, you can greatly accelerate the validation. But at the expense of more code.
I also found this article by Chris Webb helpful. But for a non-folding data source, the use of Table.Skip in the OnTake handler will not help. You have ensure that the number of rows actually retrieved from the data source is small, so as to obtain the performance improvement for the validation.

MunroA7
Advocate III

@bdarbo78 Sorry I don't mean to be rude but I've got a table with literally about 8 rows from a sharepoint list, no filters with one column and that we've changed type to text that won't load.   At all.  This simply isn't working