Forum Discussion
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 |
| Company 1 | 10 | 0.5 | 10 |
| Company 2 | 20 | 0.6 | 20 |
| Company 3 | 30 | 0.7 | 30 |
| Company 4 | 40 | 0.8 | 40 |
| Company 5 | 50 | 0.9 | 50 |
I would like to clean the data so that the data looks as follows, but I cannot work out how to unpivot / transpose / amend the unit row, before unpivotting the measure columns.
| Sub Company | Units | Attribute | Value |
| Company 1 | Number | Measure 1 | 10 |
| Company 1 | % | Measure 2 | 0.5 |
| Company 1 | Number | Measure 1=3 | 10 |
| Company 2 | Number | Measure 1 | 20 |
| Company 2 | % | Measure 2 | 0.6 |
| Company 2 | Number | Measure 1=3 | 20 |
Can anyone help?
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 !
3 Replies
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 !
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 expandUnitsValueSummary:
-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
