Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table with multiple columns for each unique row/key. Each column cell (e.g. "A: ID01") has two parts: a category (A, B, C, D) and an ID (ID01 - ID05). I'm trying to bring the unique categories to the column headers and move the IDs under the corresponding category column, if that makes sense. They will still be in the same row, just grouped under the correct category column.
Apologies for the unclear title as I wasn't sure how to describe the issue. Any tips would be appreciated!
Convert from:
Column1 | Column2 | Column3 | Column4 | |
Row1 | A: ID01 | B: ID01 | C: ID03 | |
Row2 | B: ID02 | A: ID02 | E: ID02 | |
Row3 | C: ID01 | B: ID03 | A: ID04 | |
Row4 | E: ID01 | C: ID02 | B: ID05 | A: ID03 |
To:
A | B | C | E | |
Row1 | ID01 | ID01 | ID03 | |
Row2 | ID02 | ID02 | ID02 | |
Row3 | ID04 | ID03 | ID01 | |
Row4 | ID03 | ID05 | ID02 | ID01 |
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRS8HQxMFTSUXKCs5zBLGMgSylWJxoqYQTkOsJZrnAWWIkzminGcMUmMCWuaMYbwRWbwhUbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / Table.ColumnCount(Source),0), type number}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Divided Column", "Value", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Value.1]), "Value.1", "Value.2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrRS8HQxMFTSUXKCs5zBLGMgSylWJxoqYQTkOsJZrnAWWIkzminGcMUmMCWuaMYbwRWbwhUbK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / Table.ColumnCount(Source),0), type number}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Divided Column", "Value", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Value.1]), "Value.1", "Value.2"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
Thanks Vijay! This function worked great. Only question I have is why you added indexes and then divided the columns. i.e. the steps below:
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / Table.ColumnCount(Source),0), type number}}),
As the "Index" column isn't referenced in any of the subsequent steps before being removed in the last step.
When you insert an index with base 1, it will be 1,2,3,4.....
Number.RoundUp([index]/3,0) will generate a series like
1,1,1,2,2,2,3,3,3
If you replace 3 with 5, then series would be
1,1,1,1,1,2,2,2,2,2,3,3,3,3,3
This is to a provide a column over which pivoting can be done.