rearrange table by shifting certain rows of columns to new generic columns

[Curti93]

Hello community,

I need some help..I already tried some Pivots or transpose functionalities but it doesn't really get where I need it to be.

So, here is my input table called "TYPE"

ID	Day	Hours	Type
123	01.12.2022	3	A
123	01.12.2022	1	B
123	01.12.2022	3	C
123	02.12.2022	0,5	C
124	02.12.2022	5	D
125	10.12.2022	2	B
125	10.12.2022	3	C

Now I am trying to get it into a format like this, where I have "generic" column names and the values are being "inserted" from left to right, independet of which type it is:

ID	Day	Type1Desc	Type1	Type2Desc	Type2	Type3Desc	Type3
123	01.12.2022	A	3	B	1	C	3
123	02.12.2022	C	0,5
124	02.12.2022	D	5
125	10.12.2022	B	2	C	3

Do you have any idea, how to make this happen in PQ?

If you need any further details, please let me know!

Thanks you very much in advance 🙂

[ImkeF · Accepted solution]

Hi @Curti93 
you need a nested index for the column name pairs: NestedIndex in PowerBI - YouTube
Then you can follow this description: Pivot multiple measures and columns in Power Query (thebiccountant.com)
This is a full code example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjDUMzTSMzIwMgJyDi1QMIZQjkqxOriUGEIoJzxKoKY4IysxQlVioGOKqsgEiyKoEheoEhDX0ABViRGqa7ApQbgmFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Day = _t, Hours = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Day", type date}, {"Hours", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Day"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1)}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Hours", "Type", "Index"}, {"Hours", "Type", "Index"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Expanded Partition", {"Hours", "Type"}, "Attribute", "Value"),
    #"Added Prefix" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Index", each "Type " & Text.From(_, "en-US"), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Added Prefix",{"Index", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

[Curti93]

Thank you so much for your quick response, that's very impressive! 🙂
Out of curiosity: How did you approach this situation? What was the "hint" so that you knew, I needed a nested index for the column pairs?