Forum Discussion
Solved
Group 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 A...
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
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
Hi 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 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!!!
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, Source = _t, Batches = _t, Documents = _t, Pages = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Source", type text}, {"Batches", Int64.Type}, {"Documents", Int64.Type}, {"Pages", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Source"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Source", "Attribute"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Sum)
in
#"Pivoted Column"Thanks
