cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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?

1 ACCEPTED SOLUTION
Super User

Hi,

you can obtain this

by

- transpose table

- 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]),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"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 3
Helper I

Thanks @serpiva64 - that worked perfectly. Thank you!

Super User

Hi,

you can obtain this

by

- transpose table

- 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]),
#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
#"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 !

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors