Forum Discussion

Saes's avatar
Saes
Helper I
3 years ago
Solved

Unpivot / transpose / amend a Single Row

Hi all, I have a multiple datasets (about 20 tables) that look like this - annoyingly the output puts the units in row 2. Sub Company Measure 1 Measure 2 Measure 3 Units Number % Number ...
Need Help
  • serpiva64's avatar
    serpiva64
    3 years ago

    Hi, 

    you can obtain this

     

    by

    - demote headers

    - transpose table

    - promote headers

    - unpivot columns

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLClW0lHyK81NSi0CMlQRnFidaCXn/NyCxLxKBUOgsKEBkDDQM4UwkWWNgEJGEFkzCBNZ1hgoZAyRNYcwkWVNgEImEFkLCBNZFmSZKUTWEsKMjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sub Company" = _t, #"Measure 1" = _t, #"Measure 2" = _t, #"Measure 3" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Sub Company", "Units"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "SubCompany"}, {"Sub Company", "Attribute"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"SubCompany", Order.Ascending},{"Attribute", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"SubCompany", "Units", "Attribute", "Value"})
    in
    #"Reordered Columns"

     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !

     

BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi Saes ,

 

Here's one way to do it. It involves a few custom steps/adjustments but is fairly simple and tidy:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLClW0lHyK81NSi0CMlQRnFidaCXn/NyCxLxKBUOgsKEBkDDQM4UwkWWNgEJGEFkzCBNZ1hgoZAyRNYcwkWVNgEImEFkLCBNZFmSZKUTWEsKMjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sub Company" = _t, #"Measure 1" = _t, #"Measure 2" = _t, #"Measure 3" = _t]),
    unpivOthCols = Table.UnpivotOtherColumns(Source, {"Sub Company"}, "Attribute", "Value"),
    getUnitsTable = Table.SelectRows(unpivOthCols, each [Sub Company] = "Units"),
    getDataTable = Table.SelectRows(unpivOthCols, each [Sub Company] <> "Units"),
    mergeUnitsOnData = Table.NestedJoin(getDataTable, {"Attribute"}, getUnitsTable, {"Attribute"}, "getDataTable", JoinKind.LeftOuter),
    expandUnitsValue = Table.ExpandTableColumn(mergeUnitsOnData, "getDataTable", {"Value"}, {"Value.1"})
in
    expandUnitsValue

 

Summary:

-1- Unpivot columns other than [Sub Company]

-2- Split into two tables, one with just [Sub Company] = "Units", the other with everything else

-3- Merge Units table onto Other Data table on U.Attribute = OD.Attribute

-4- Expand OD.Value

 

Output:

 

Pete