PowerQuery - Validating queries when saving is taking excessively long time

[uberdube]

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.

[thecoolnine9]

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]

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]

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]

It is dead this afternoon. Same here.

[graemejohnson]

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

[Anonymous]

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

[tuken]

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

[Gulan70]

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

[bdarbo78]

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.

[Anonymous]

@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