none
Group data and sort rows inside group RRS feed

  • Question

  • Dear All,

    In Power Query I am looking for an elegant solution to group the first three columns of my dataset (including all rows) and inside this grouped data sort by timestamp and another column called order. After that I would like to pick the first row of the grouped rows. Anybody a good solution for this in powerquery?

    Thanks Michiel Soede

    Sunday, February 3, 2019 4:32 PM

Answers

  • Hi Michiel,

    You need to add three steps to your query, e.g.

    GroupedRows = 
           Table.Group(
              <PreviousStepName>, 
              {"Column1", "Column2", "Column3"}, 
              {"Tables", each _, type table}
           ),
    SortedRows = 
        Table.TransformColumns(
            GroupedRows, 
            {
              "Tables", 
              each Table.Sort(_, {"timestamp", "order"})
            }
        ),
    FirstRows = 
        Table.TransformColumns(
            SortedRows, 
            {
              "Tables", 
              each Table.First(_)
            }
        )

    • Proposed as answer by Colin Banfield Sunday, February 3, 2019 6:24 PM
    • Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM
    Sunday, February 3, 2019 5:54 PM
  • Glad that the solution worked out. I meant to include the default ordering, so you could see where it needed changing if you didn't want to order by ascending values, but you figured it out!

    You could also combine the sort operation with the group step:

    GroupedRows = 
           Table.Group(
              <PreviousStepName>, 
              {"Column1", "Column2", "Column3"}, 
              {"Tables", each Table.Sort(_, {{"timestamp", Order.Descending}, "order"}), type table}
           ),
    FirstRows = 
        Table.TransformColumns(
            GroupedRows, 
            {
              "Tables", 
              each Table.First(_)
            }
        )

    • Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM
    Sunday, February 3, 2019 6:41 PM
  • Well - each Table.First(_) is the same as each (_){0}. So the second row would be each (_){1}. If you want the first two rows, then each Table.FirstN(_, 2).
    • Marked as answer by Michiel Soede Monday, February 4, 2019 12:48 PM
    Monday, February 4, 2019 12:47 PM

All replies

  • Hi Michiel,

    You need to add three steps to your query, e.g.

    GroupedRows = 
           Table.Group(
              <PreviousStepName>, 
              {"Column1", "Column2", "Column3"}, 
              {"Tables", each _, type table}
           ),
    SortedRows = 
        Table.TransformColumns(
            GroupedRows, 
            {
              "Tables", 
              each Table.Sort(_, {"timestamp", "order"})
            }
        ),
    FirstRows = 
        Table.TransformColumns(
            SortedRows, 
            {
              "Tables", 
              each Table.First(_)
            }
        )

    • Proposed as answer by Colin Banfield Sunday, February 3, 2019 6:24 PM
    • Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM
    Sunday, February 3, 2019 5:54 PM
  • It is really astonishing what you can reach with so little code. This works like a charm. Many thanks for this solution. 

    Only thing I added to it was the sort order for the timestamp (where I needed the most recent record):

    SortedRows = Table.TransformColumns(GroupedRows, {"Tables", each Table.Sort(_, {{"Timestamp",Order.Descending}, "Order"})}),

    Regards!

    Michiel

    Sunday, February 3, 2019 6:21 PM
  • Glad that the solution worked out. I meant to include the default ordering, so you could see where it needed changing if you didn't want to order by ascending values, but you figured it out!

    You could also combine the sort operation with the group step:

    GroupedRows = 
           Table.Group(
              <PreviousStepName>, 
              {"Column1", "Column2", "Column3"}, 
              {"Tables", each Table.Sort(_, {{"timestamp", Order.Descending}, "order"}), type table}
           ),
    FirstRows = 
        Table.TransformColumns(
            GroupedRows, 
            {
              "Tables", 
              each Table.First(_)
            }
        )

    • Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM
    Sunday, February 3, 2019 6:41 PM
  • What else? ;-)

    That makes it even shorter!

    thanks for the support 

    Sunday, February 3, 2019 7:07 PM
  • "What else? ;-)"

    Well given that the solutions work for you, could you mark either one or both of then as answered? ;-) This saves the forum moderators from having to bug you in the future, as the preferred status of a thread is to be answered.

    Sunday, February 3, 2019 7:36 PM
  • Haha, sorry for that! Done
    Monday, February 4, 2019 10:49 AM
  • One last question to complete the topic :-), would it also be possible to select the second row of the grouped rows? My line of thinking would be to refer to {1} with Table.SelectRow instead of the Table.First?

    Thanks, 

    Michiel

    Monday, February 4, 2019 12:18 PM
  • Well - each Table.First(_) is the same as each (_){0}. So the second row would be each (_){1}. If you want the first two rows, then each Table.FirstN(_, 2).
    • Marked as answer by Michiel Soede Monday, February 4, 2019 12:48 PM
    Monday, February 4, 2019 12:47 PM
  • Thanks!
    Monday, February 4, 2019 12:48 PM