Power Query - Table.Group & Text.Combine

[Gwill1983]

Hi all,

I have previously used these features to create an order summary report as below:

Customer	Email Address	Orders
Joe Bloggs		380370
Jon Smith		380139; 380004
Alan Partridge		380536; 380621

This table uses the syntax = Table.Group(#"Filtered Rows", {"Supplier", "Email Address"}, {{"Outstanding Purchase Orders", each Text.Combine([PO Number], "; "), type nullable text}})

The raw data that we are provided now breaks each order down into outstanding items so I would like to adapt this to include the items when creating this report. Ideally this would look something like the below:

Customer	Email Address	Orders	Items
Joe Bloggs		380370	Part A; Part B; Part C
Jon Smith		380139; 380004	Part C; Part D x 2
Alan Partridge		380536; 380621	Part A x 3

The column in the raw data is named 'Part Description', but I cannot work out how to add the items column and then combine the parts to show multiples etc. I have tried to re-add the {{"Outstanding Purchase Orders", each Text.Combine([PO Number], "; ") section of the syntax and adjust this, but it doesnt work.

If anybody has any ideas how to make this work you would be my hero!

 

[RoryA]

You need to add a new list within the list:

= Table.Group(#"Changed Type", {"Supplier", "Email Address"}, {{"Outstanding Purchase Orders", each Text.Combine(List.Distinct([PO Number]), "; "), type nullable text}, {"Item list", each Text.Combine([Items], "; "), type nullable text}})

I added List.Distinct as I guess you need to avoid duplicates in the Orders column

 

[Gwill1983]

You need to add a new list within the list:

= Table.Group(#"Changed Type", {"Supplier", "Email Address"}, {{"Outstanding Purchase Orders", each Text.Combine(List.Distinct([PO Number]), "; "), type nullable text}, {"Item list", each Text.Combine([Items], "; "), type nullable text}})

I added List.Distinct as I guess you need to avoid duplicates in the Orders column

Thanks for this.

It works exactly how I wanted it to.

Do you know if there is anyway of ensuring that duplicates in the item column show as multiples rather than duplicate entries? For instance, at the moment, if a customer had 3 orders outstanding all containing the same item it would show as below:

Customer	Email Address	Orders	Items
Joe Bloggs		380370; 380371; 380372	Part A; Part A; Part A

Is it possible to adjust this to show as this:

Customer	Email Address	Orders	Items
Joe Bloggs		380370; 380371; 380372	Part A x 3

 

[RoryA]

Yes - something like:

= Table.Group(#"Changed Type", {"Supplier", "Email Address"}, {{"Outstanding Purchase Orders", each Text.Combine(List.Distinct([PO Number]), "; "), type nullable text}, {"Item list", each Text.Combine(List.Distinct(List.Transform([Items], (y)=> let cnt = List.Count(List.Select(_[Items], (x)=>x=y)) in if cnt = 1 then y else y & " x " & Text.From(cnt))), "; "), type nullable text}})

should work. (there may well be simpler options; I'm not an M guru!)

 

[Gwill1983]

Perfect! You seem to be an M guru to me!

Thanks for your help

 

[RoryA]

Glad we could help!