Forum Discussion

PVO3's avatar
PVO3
Impactful Individual
4 years ago

Unpivot other columns breaks query folding

Hello,

 

I would really like to get some help. For some queries I really depend on query folding, otherwise the entire refresh times out.

 

When using a MYSQL or SQL Server source, 'Unpivot other columns' results in breaking query folding. According to the documentation most sources should support query folding and pivoting is one of the supported actions. But, in my case I get the follow results:

- SQL Server Source: Query Folding only works with → Unpivot Columns & Unpivot only selected columns

- MySQL Source: All of the pivoting actions will break Query Folding.

 

Example

 

let
    Source = MySQL.Database("server.servernet.local", "database", [ReturnSingleDatabase=true]),
    tablename = Source{[Schema="database",Item="table"]}[Data],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(tablename , {"Columns")
in
    #"Unpivoted Columns"

 

Is there a solution, a fix, a workaround, anything to make pivoting (especially Unpivot Other columns) foldable from a MySQL or SQL source? Without changing the source.

 

Need Help

2 Replies

  • jennratten's avatar
    jennratten
    Super User
    4 years ago

    Hello - Table.UnpivotOtherColumns does not seem to fold in PowerBI desktop but in dataflows, if the enhanced compute engine is enabled Table.UnpivotOtherColumns does fold.

    The M script: 

     

    let
    QueryResult = Table.UnpivotOtherColumns(Customers, {"Id", "Name", "Age", "ZipCode"}, "Attribute", "Value")
in
    QueryResult

     

    is converted to the foldable script:

     

    execute sp_executesql N'select [$Table].[Id],
    [$Table].[Name],
    [$Table].[Age],
    [$Table].[ZipCode],
    [$Table].[Attribute],
    [$Table].[Value]
from [dbo].[Customers] [$Pivot] unpivot ([Value] for [Attribute] in ([Country])) as [$Table]'

     

     

    Here is a good resource on folding with dataflows and the enhanced compute engine:

    https://petcu40.blogspot.com/2019/06/m-folding-in-enhanced-engine-of-power.html 

    • PVO3's avatar
      PVO3
      Impactful Individual
      4 years ago

      Thanks a lot for your suggestion!

      Unfortunatly this still is a change of the source. At the moment I don't have access to Dataflows nor Premium.

      If there is no other solution, then this will indeed problably be the best alternative.