Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PietBelcrum
Frequent Visitor

Simple text problem?

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. 20240111_AvailableSizes.jpg

 

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

 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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

View solution in original post

3 REPLIES 3
slorin
Super User
Super User

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
Super User
Super User

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors