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 someti...
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

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.