Forum Discussion
Helper IGroup like data onto single row
Greetings, thank you for looking at my post.
I am being given data in the following format
| Date | Group | Type1 | Type2 | Type3 |
| 1/1/24 | Apple | 12 | 18 | 26 |
| 1/1/24 | Banana | 9 | 15 | 21 |
| 2/1/24 | Apple | 15 | 17 | 36 |
| 2/1/24 | Banana | 16 | 7 | 13 |
I need to convert the data (preferably in Power Query Transform) to look like the format below
| Date | Apple1 | Apple2 | Apple3 | Banana1 | Banana2 | Banana3 |
| 1/1/24 | 12 | 18 | 26 | 9 | 15 | 21 |
| 2/1/24 | 15 | 17 | 36 | 16 | 7 | 13 |
I am drawing a blank in my head how to do this. Any insight or advice would be greatly appreicated. Thank you again for looking at my post.
Hi Gusd8 ,
Use following M code.let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyUdJRciwoyEkF0oZGIMICSBiZKcXqIKlwSswDQiDDEqTCFKTCEKzCCMMMkKShOZAwNkNVATfD0AxIgFQYGivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Group = _t, Type1 = _t, Type2 = _t, Type3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Group", type text}, {"Type1", Int64.Type}, {"Type2", Int64.Type}, {"Type3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Group"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Group"}, {{"Count", each _, type table [Date=nullable date, Group=nullable text, Value=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Value", "Index"}, {"Value", "Index"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-AU"),{"Group", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
#"Pivoted Column"Thanks
4 Replies
AUDISUResolver III
Hi Gusd8 ,
Use following M code.let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyUdJRciwoyEkF0oZGIMICSBiZKcXqIKlwSswDQiDDEqTCFKTCEKzCCMMMkKShOZAwNkNVATfD0AxIgFQYGivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Group = _t, Type1 = _t, Type2 = _t, Type3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Group", type text}, {"Type1", Int64.Type}, {"Type2", Int64.Type}, {"Type3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Group"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Group"}, {{"Count", each _, type table [Date=nullable date, Group=nullable text, Value=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Value", "Index"}, {"Value", "Index"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-AU"),{"Group", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
#"Pivoted Column"Thanks
Gusd8Hi AUDISU I have to apologize, your sloulution did EXACTLY what I asked. My intent was to take the insight I was provided and then engineer it to work with the data I have. The "index" works to provide what I had asked...but sadly I didn't correctly ask what I needed. For this I am sorry. What I am really wanted is listed below. I hope it is more precise and easily achived. Your reply was brilliant and I am excited about another solution you may have for me. Thank you in advance for sharing your knowledge!!
Date Source Batches Documents Pages 1/1/24 Apple 12 18 26 1/1/24 Banana 9 15 21 2/1/24 Apple 15 17 36 2/1/24 Banana 16 7 13 and I want it to look like...
Date Apple_Batches Apple_Documents Apple_Pages Banana_Batches Banana_Documents Banana_Pages 1/1/24 12 18 26 9 15 21 2/1/24 15 17 36 16 7 13 - Gusd8
AUDISU You know what...I cheated...I used the index and I renamed the columns as the last step (since the column headers in this instance will always be static). I think it would still be very cool if there was a solution that was dynamic in case teh column headers are not static...but I am marking this solved as I was able to make it work.
Thank You VERY MUCH for your help with this!!!