locked
Table.Pivot -> pivot on dimension column but I have more than one fact column RRS feed

  • Question

  • Hello,

    I have an input table, which I need to pivot as below:

    I managed to obtain this via following syntax, but I have serious doubts whether this is the best way (regarding performance) to do things.

    Applied logic:

    1. Do the pivot on column Product for Amount
    2. Do the same pivot (on column Product) for Units
    3. Merge (join) both resultsets

    Syntax:

    let
        Source = Excel.Workbook(File.Contents("Y:\PowerBI\input for pivot.xlsx")),
        Input_Table = Source{[Item="Input",Kind="Table"]}[Data],

        Input_Units = Table.RemoveColumns(Input_Table,{"Amount"}),
        Input_Amounts = Table.RemoveColumns(Input_Table,{"Units"}),
        Pivot_Amounts = Table.Pivot(Input_Amounts,{"Bike","Car"},"Product","Amount", List.Sum),
        Pivot_Units = Table.Pivot(Input_Units,{"Bike","Car"},"Product","Units", List.Sum),
        Pivots_Joined = Table.Join(Table.PrefixColumns(Pivot_Amounts, "Amount"), "Amount.DateKey",Table.PrefixColumns(Pivot_Units, "Unit"), "Unit.DateKey", JoinKind.FullOuter), 
        Result = Table.RenameColumns(Table.RemoveColumns(Pivots_Joined,{"Unit.DateKey"}),{{"Amount.DateKey", "DateKey"}})
    in
        Result

    My question:

    Anybody an idea if possible and if yes, how to write it in a single Table.Pivot statement?

    Any help or a good starting point would be highly appreciated, as I will have to do this on more than 6 rows in the very near future...

    (apart from Chris Webb's super blog I couldn't find proper information on Table.Pivot function)

    Cheers,

    Tom


    Tuesday, May 5, 2015 3:39 PM

Answers

  • Hi Tom,

    yes, you can apply a more universal approach here:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
           // Unpivot Fact Cols to create unique Headers
        Unpivot = Table.UnpivotOtherColumns(Source, {"Datekey", "Product"}, "Attribute", "Value"),
           // Define new Header fields
        AddNewHeader = Table.AddColumn(Unpivot, "key", each [Attribute]&"."&[Product]),
           // Remove old columns
        RemoveCols = Table.RemoveColumns(AddNewHeader,{"Product", "Attribute"}),
           // Sort according to your request
        Sort = Table.Sort(RemoveCols,{{"key", Order.Ascending}}),
           // enjoy :-)
        Pivot = Table.Pivot(Sort, List.Distinct(Sort[key]), "key", "Value", List.Sum)
    in
        Pivot


    Imke

    Tuesday, May 5, 2015 5:05 PM

All replies

  • Hi Tom,

    yes, you can apply a more universal approach here:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
           // Unpivot Fact Cols to create unique Headers
        Unpivot = Table.UnpivotOtherColumns(Source, {"Datekey", "Product"}, "Attribute", "Value"),
           // Define new Header fields
        AddNewHeader = Table.AddColumn(Unpivot, "key", each [Attribute]&"."&[Product]),
           // Remove old columns
        RemoveCols = Table.RemoveColumns(AddNewHeader,{"Product", "Attribute"}),
           // Sort according to your request
        Sort = Table.Sort(RemoveCols,{{"key", Order.Ascending}}),
           // enjoy :-)
        Pivot = Table.Pivot(Sort, List.Distinct(Sort[key]), "key", "Value", List.Sum)
    in
        Pivot


    Imke

    Tuesday, May 5, 2015 5:05 PM
  • Imke, you rock!
    Wednesday, May 6, 2015 8:45 AM