Forum Discussion
Pivoting the table data in Modeling
Hi,
I am unable to get the below output using Pivot table option.
Table.
| ID | Attrib | Value |
| 82 | Section | A |
| 82 | Section | B |
| 82 | Class | 2 |
| 83 | Section | A |
| 83 | Class | 3 |
Output Table;
| ID | Section | Class |
| 82 | A | 2 |
| 82 | B | 2 |
| 83 | A | 3 |
If I use the pivot table option in the modeling to convert the Atrrib column values as columns against each ID, getting an error where we have extract of multiple values attribute (Section in this case).
How to convert Section as new column retaining multiple values as multiple rows for particular IDs.
VijayRbn Hi! Here the M code for the advanced editor:
let
// Load the original data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjBS0lEKTk0uyczPA7IclWJ1MASdEILOOYnFxUDaCCJkjE2zMZI6Y6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Attrib = _t, Value = _t]),
// Group the data by ID and Attrib to handle multiple rows for the same key
GroupedTable = Table.Group(
Source,
{"ID", "Attrib"},
{{"Values", each Text.Combine([Value], ","), type text}}
),// Pivot the grouped table based on the 'Attrib' column
PivotedTable = Table.Pivot(
GroupedTable,
List.Distinct(GroupedTable[Attrib]), // Pivot by the unique values in 'Attrib' (i.e., 'Section' and 'Class')
"Attrib",
"Values"
),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(PivotedTable, {{"Section", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Section"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"Section", type text}})
in
#"Changed Type"you'll achieve:
if it's ok, please accept my answer as solution.
BBF
2 Replies
VijayRbn Hi! Here the M code for the advanced editor:
let
// Load the original data
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjBS0lEKTk0uyczPA7IclWJ1MASdEILOOYnFxUDaCCJkjE2zMZI6Y6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Attrib = _t, Value = _t]),
// Group the data by ID and Attrib to handle multiple rows for the same key
GroupedTable = Table.Group(
Source,
{"ID", "Attrib"},
{{"Values", each Text.Combine([Value], ","), type text}}
),// Pivot the grouped table based on the 'Attrib' column
PivotedTable = Table.Pivot(
GroupedTable,
List.Distinct(GroupedTable[Attrib]), // Pivot by the unique values in 'Attrib' (i.e., 'Section' and 'Class')
"Attrib",
"Values"
),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(PivotedTable, {{"Section", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Section"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID", Int64.Type}, {"Section", type text}})
in
#"Changed Type"you'll achieve:
if it's ok, please accept my answer as solution.
BBF
That really helped my Puzzle! Thanks a lot. I was unaware of the Group concept, that would help in my learning too.
