Forum Discussion

PietBelcrum's avatar
PietBelcrum
Frequent Visitor
2 years ago
Solved

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.

 

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

 

Need Help
Show and Tell
  • slorin's avatar
    slorin
    2 years ago

    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

3 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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's avatar
      PietBelcrum
      Frequent Visitor
      2 years ago

      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.

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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