Modifying table view in Power Query

[Anonymous]

I have the table in the below format

EmplID	School	Degree	Major
123	S1	D1	M1
123	S2	D2	M2
123	S3	D3	M3
456	S1	D1	M1
456	S2	D2	M2
456	S3	D3	M3

I would want to change it to,

EmplID	School1	Degree 1	Major 1	School2	Degree 2	Major 2	School3	Degree 3	Major 3
123	S1	D1	M1	S2	D2	M2	S3	D3	M3
456	S1	D1	M1	S2	D2	M2	S3	D3	M3

Can someone suggest how we can achieve this in Power BI Desktop or using Query Editor?

[Anonymous]

Thank you @slorin. This helped!

[slorin · Accepted solution]

Hi,

let
Source = YourSource,
Group = Table.Group(Source, {"EmplID"}, {{"Data", each Table.AddIndexColumn(_,"Index",1)}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"School", "Degree", "Major", "Index"}, {"School", "Degree", "Major", "Index"}),
UnPivot = Table.UnpivotOtherColumns(Expand, {"EmplID", "Index"}, "Attribute", "Value"),
CombineColumns = Table.CombineColumns(Table.TransformColumnTypes(UnPivot, {{"Index", type text}}),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Attribute.1"),
Pivot = Table.Pivot(CombineColumns, List.Distinct(CombineColumns[Attribute.1]), "Attribute.1", "Value")
in
Pivot

Stéphane