Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.