Forum Discussion

squarecat's avatar
squarecat
Helper I
1 year ago
Solved

Advanced unpivoting multiple columns

Hello, I have this source table        PLAN PLAN PLAN ACT ACT ACT LY LY LY ID Cost Center Jan Feb Mar Jan Feb Mar Jan Feb Mar 123 A 100 90 120 90 110 80 60 150...
Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    Starting with data formatted like...

    You can end up with...

    with the following code...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4/C8IwEMW/SsncIf8uiWNNEZQqDi4SMlToWkH7/fEuJqno4nC/vHfw3iUE1rA2zXnoTj9P5y9fHK4fiG1g+x61vz+Xxk/zMj3QHcYZuZtuyOP434aqhFR0B0dwjtwQhFylIDqCSR6IFlJYKo1mi5O2BhCaFwBZS0pBiWMRBZWmjX835XoHBek61AqXuykIxqLpS52un1uVqT2Qfx3jCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    new_columns = 
    List.Transform(List.Zip({Record.ToList(Table.FirstN(Source, 2){1}), Record.ToList(Table.FirstN(Source, 2){0})}), each if _{1} = " " then _{0} else Text.Combine(_, "-")),
    rename_list = 
    List.Zip({List.Generate(()=>1, each _ <= List.Count(new_columns), each _ + 1, each "Column" & Text.From(_)), new_columns}),
    edit_table = 
    Table.RenameColumns(Table.Skip(Source, 2), rename_list),
    unpivot_other_columns = 
    Table.UnpivotOtherColumns(edit_table, {"ID", "Cost Center"}, "Attribute", "Value"),
    split_attribute_column = 
    Table.SplitColumn(unpivot_other_columns, "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Date", "Type"}),
    pivot_type_column = 
    Table.Pivot(split_attribute_column, List.Distinct(split_attribute_column[Type]), "Type", "Value"),
    transform_date = 
    Table.TransformColumns(pivot_type_column,{{"Date", each Date.FromText("01"&_&"2025"), type date}}),
    table_sort = 
    Table.Buffer(Table.Sort(transform_date, {{"Date", Order.Ascending}, {"ID", Order.Ascending}, {"Cost Center", Order.Ascending}}))
in
    table_sort
lbendlin's avatar
lbendlin
Super User
1 year ago

Your source pivot table is not in a usable format, especially taking into account that it will likely get wider through the course of the year.  See if you can get it in a more suitable format.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4/C8IwEMW/SsncIf8uiWNNEZQqDi4SMlToWkH7/fEuJqno4nC/vHfw3iUE1rA2zXnoTj9P5y9fHK4fiG1g+x61vz+Xxk/zMj3QHcYZuZtuyOP434aqhFR0B0dwjtwQhFylIDqCSR6IFlJYKo1mi5O2BhCaFwBZS0pBiWMRBZWmjX835XoHBek61AqXuykIxqLpS52un1uVqT2Qfx3jCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    Months = {1..(List.Count(Table.ColumnNames(Source))-2)/3},
    #"Converted to Table" = Table.FromList(Months, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Columns", each {"Column1","Column2"} & List.Transform({[Column1]*3..[Column1]*3+2},each "Column" & Text.From(_))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Data", each let s = Table.SelectColumns(Source,[Columns]) in Table.AddColumn(Table.PromoteHeaders(Table.Skip(s,1), [PromoteAllScalars=true]), "Type", each List.Max(Record.ToList(s{0})))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"ID", "Cost Center", "Jan", "Feb", "Mar", "Type"}, {"ID", "Cost Center", "Jan", "Feb", "Mar", "Type"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Data", {"ID", "Cost Center", "Type"}, "Month", "Value")
in
    #"Unpivoted Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

 

As you can see your months are sorted alphabetically. Your data model should include a calendar table.