Forum Discussion

009co's avatar
009co
Icon for Helper IV rankHelper IV
3 years ago

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

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 ne...
Dataflows
Need Help
miguel's avatar
miguel
Icon for Community Admin rankCommunity Admin
3 years ago

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's avatar
    009co
    Icon for Helper IV rankHelper IV
    3 years ago

    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's avatar
      miguel
      Icon for Community Admin rankCommunity Admin
      3 years ago

      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's avatar
        009co
        Icon for Helper IV rankHelper IV
        3 years ago

        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