Dataflow Gen 2 dataflow queries with PIVOT long running, never return results

[009co]

My Dataflow Gen 2 dataflow query does following:

* Sources from 4 Lakehouse tables each with 3 columns - datetime, string, number.

* Appends 4 tables as new (about 4,500 rows total).

* Pivot newly appended results by the string column to create columns for each string value containing their respective number values.

However, this has so far never returned results, just runs "forever". Have seen same behavior with similar GROUP BY query. When I Publish the query the refreshing circle is to right of object name which seems to indicate it is not yet refreshing but being saved / published.

Is there any known reasons why this might be the case?

The same queries in Power BI desktop and Power BI online datasets and "regular" dataflows complete quickly.

[miguel]

@009co would it be possible for you to share some repro steps so that we can try and see what might be going on? perhaps sharing the data and the queries that you've created would help us tremendously. You can send me a direct message if needed so we can talk further about this

[009co]

Hi thanks, here is the query m-code.

* If Pivot step is removed the query completes successfully, otherwise it doesn't has described in this thread.

* The combined / appended tables have about 4500 records.

* Parameter column has 3 distinct values eg pivots to give 3 columns..

let

  Source = Table.Combine({#"2011_to_2021_level_and_discharge", #"2013_to_2023_precipitation", #"2021_to_present_discharge", #"2021_to_present_level"}),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Parameter", type text}, {"Value", type number}, {"Date", type datetime}}),

  #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type", {{"Parameter", type text}}), List.Distinct(Table.TransformColumnTypes(#"Changed column type", {{"Parameter", type text}})[Parameter]), "Parameter", "Value")

in

  #"Pivoted column"

[miguel]

could you please share your dataset with us so we could try and repro this scenario?

The only thing that I can think of is that your Parameter column might have a high cardinality where it tries to create over 250 new columns using the Pivot operation, but its something that we would love to learn from and improve the experience.

[009co]

Hey miguel, it appears that Dataflow Gen 2 simply cannot do the pivot transform. My guess is that it is related to lack of resources. But maybe it is just on my trial account? But are you able to use the data I linked below to replicate my steps?

[miguel]

Hey! sorry for the late reply.

We're working on some improvements to the overall architecture behind the scenes. Will update this thread once I have more information that I can share, but thanks for sharing the info!

[009co]

Thanks for following up.

Cardinality is low. Parameter column has only 3 distinct values eg pivots to give 3 columns. It completes quickly successfully in Power BI Desktop.

I created Dataset containing the 4 Lakehouse Tables, then exported Dataset tables to csv and put them here https://github.com/sitrucp/Deadman-River

Test:

* Upload csv files to Lakehouse Tables

* Create Dataflow Gen2 query that 

   => appends 4 Tables

   => Pivot appended data on Parameter column with Value resulting in one row per Date with 3 columns (Discharge, Level, Precipitation)

   => Data destination to new Lakehouse Table.

   => Run Dataflow Gen2

Edit to add:

I actually did the test described above in a brand new Workspace, Lakehouse, Dataflow Gen2, etc and got same results eg  the dataflow with the pivot will not complete and not ever create new Lakehouse Table.

-----------------------------
More details on dataflow publish fail / message that I get:

The dataflow could not be saved. The Model evaluation was cancelled.

Please try again later.

Session ID: 9b7d629f-404f-0562-9575-1095d2ed09ee
Root activity ID:: 31686fac-a385-4266-bdb8-054688650a55
Time: 2023-06-05T13:59:40.677Z

[miguel]

Hi!

When you mention that this has never returned results, you mean during a refresh? or while you were inside the Power Query editor?

Could you take a screenshot of the query plan at the last step of your query? More information about the query plan feature below and how to get it:

Query plan - Power Query | Microsoft Learn

[009co]

When you mention that this has never returned results, you mean during a refresh? or while you were inside the Power Query editor?

Both.

Below is the dataflow query (named Data), that appends 4 tables and pivots on string column, waiting to show results. Note that the Source step does quickly show the appended results. It is the Pivot step that is the issue.

Below is the dataflow (named Dataflow 2) which is the above but Published, waiting to show results. 

Below is screenshot of the above screen after maybe 20 or 30 minutes when it stops, shows the highlighted red triangle beside dataflow name, that pops the message about "The dataflow was could not be saved. The Model evaluation was cancelled. Please try again later."

Below is query plan which is also waiting to show results (I guess the query has to return results before the query plan can be shown?)