Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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 🙂
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?