Pivoting the table data in Modeling

[VijayRbn]

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.

[BeaBF · Accepted solution]

@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

[VijayRbn]

That really helped my Puzzle! Thanks a lot. I was unaware of the Group concept, that would help in my learning too.