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.

[Anonymous]

I am experiencing the same issue here. This is my first time attempting to use dataflows, so I thought I must be doing something wrong.

I basically have a large set (over 10 million rows) of data that remains static and does not need refreshed. Dataflows seemed like the ideal solution.

I am connecting to our enterprise data warehouse through an oracle SQL query. The query is rather complex, but there are no transformation steps after the source query.  The query works in Power BI Desktop just fine, but times out in the dataflow.

This is beyond frustrating.

[waltercalcagno]

Same issue here  

[altbra]

The performance has become normal since yesterday. I mean not super slow but just slow. But the behavior of the validation is still not reliable during working hours. A validation which takes 1 minute at night could be validated over 20-30 minutes in working hours.

@Anonymous Please, do not post about 10mln records to be validated. Just parametrize you query with select top(@rows) from ... and use Table.View to redifine the OnTake method. The dataflows IDE sends Table.FirstN in order to show you first rows of the query result. 

[Daryl-Lynch-Bzy]

@altbra - could you please expand on the comment to @Anonymous .  I am familiar with the Table.View approach described in Chris Webb BI Blog ( @cwebb ), but I am interest to understand how you thinks this helps with the Power BI Dataflows "Save & Close" validation process.

I have a Dataflow that reads an Excel file from Sharepoint.  Recently, I need to change the Dataflow because the Excel file moved from starting on row 10 to starting on row 6.  I am only pulling 121 x 5 columns of data, so my actual change did not impact the Output schema.  The Dataflow refresh time is 5 seconds max.  However, I still had to wait 30 mins for the "Save & Close" validation.

[altbra]

@Daryl-Lynch-Bzy

If you use a Value.NativeQuery over 10mln records without folding (out-of-the-box or with using some logic within Table.View) the validation process will be downloading all 10mln rows instead of just metadata. This is not an issue of the Dataflows IDE

Your case with the case which I am talking about. The IDE is dieing during working hours. Your query will be validated in a second aroung midnight.

[altbra]

Plus 20 votes within couple days.  Hopefully MS will notice this finally. 

[michaelsh]

What is going on!
It cannot continue this way.

Hours of work lost

Please fix!

[Gulan70]

Dataflows should make our lives more simple. Now it's so frustrating that I regret starting with it.

[jkommeren]

Been trying all day to save a minor change in query (adding of two values, whoop whoop!). Let it run for 2 hours, optimized query a little, tried again, etc.

Even if it is a capacity problem, it seems a little retarded to run a "full" check it seems to be doing? I mean the data in the database changes constantly right, what's the point in performing the ultimate validation? (I mean it's not just checking the database column types, right?)

At least

- let us save the queries first, close the screen, and perform the check on the background instead of blocking the window.

- Progress indicator would help too, if that shows it's trying over and over again, I'd know I could safely cancel it without losing "progress"

[BumblebeeOT]

Same issue here.

Just one simple change takes more than 4 hours to validate.

Besides problems with dataflow refreshes, sometimes work, the following refresh not, next yes...