Simple text problem?

[PietBelcrum]

This is my first topic as I am new in PowerQuery. My problem seems very simple, but I didn't found the solution in examples.
I have different products and each product has different sizes and sometimes stock.

My table has three columns: The type of the product, the size and the stock.

I would like to have an extra fourth column with all the available sizes of one product.

let
Bron = Excel.CurrentWorkbook(){[Name="tblAvailableSizes"]}[Content],
TypeGewijzigd = Table.TransformColumnTypes(Bron,{{"product", type text}, {"size", type text}, {"stock", Int64.Type}}),
RijenGesorteerd = Table.Sort(TypeGewijzigd,{{"product", Order.Ascending}, {"size", Order.Ascending}})
in
RijenGesorteerd

[slorin]

Hi,

A few seconds to find the solution, a few minutes to write the answer

but several years to master Power Query (and not all of them !)

Stéphane

[slorin · Accepted solution]

Hi,

let
Bron = Excel.CurrentWorkbook(){[Name="tblAvailableSizes"]}[Content],
TypeGewijzigd = Table.TransformColumnTypes(Bron,{{"product", type text}, {"size", type text}, {"stock", Int64.Type}}),
RijenGesorteerd = Table.Sort(TypeGewijzigd,{{"product", Order.Ascending}, {"size", Order.Ascending}}),
Group = Table.Group(RijenGesorteerd, {"product"}, 
      {{"Data", each _, type table [product=text, size=number, stock=nullable number]}, 
       {"available", each Text.Combine(Table.SelectRows(_, each [stock]<>0 and [stock]<>null)[size], ", "), type text}}),
Expand = Table.ExpandTableColumn(Group, "Data", {"size", "stock"}, {"size", "stock"})
in
Expand

 Stéphane

[PietBelcrum]

Hi Stéphane,
This is my first post. I am very happy and totally buffled that you solved it by grouping and expanding. I didn't know that you could expand things.
Can you tell me how much time it costs you to solve the problem?
If have a another problem, and I have, I would like to post it as well.
Many thanks.