Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
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.
Check out the July 2025 Power BI update to learn about new features.