Group like data onto single row

[Gusd8]

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.

[AUDISU · Accepted solution]

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

[Gusd8]

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

[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!!!

[AUDISU]

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