none
Search by Rows and Group RRS feed

  • Question

  • Have sample data like the following

    Product   Product Description Quantity

    Product1 Apples  2

    Rows of info here of variable length

    Product2 Bananas  3

    Rows of info here of variable length

    Product3 Pears 3

    Rows of info here of variable length

    Product1 Apples 2

    Rows of info here of variable length

    I'm trying to work how I can  "group" rows by product and the associate Rows of Info and add a new column for each group

    Does anyone know if this is possible with Power Query.  

    Cheers

    Steve

    Tuesday, August 9, 2016 7:57 AM

Answers

All replies

  • SF,

    Is this similar to what you want to do ?  Many thanks.

    let
        Source = Excel.CurrentWorkbook(){[Name="FruitStand"]}[Content],
        #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}, {"Column2", "Nama"}, {"Column3", "Data"}}),
        #"Grouped Rows" = Table.Group(#"Renamed Columns", {"ID"}, {{"Count", each Table.RowCount(_), type number}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [ID] & "New Column Data ")
    in
        #"Added Custom"

    Tuesday, August 9, 2016 9:53 AM
  • Hi Steve. Have you tried right-clicking the product column and selecting Group By? Once you've grouped the various tables, you should be able to join them using Merge.

    Ehren

    Tuesday, August 9, 2016 5:07 PM
    Owner