none
Complex filter RRS feed

  • Question

  • I have lots of columns, but the 3 in question are

    • Name (A or B)
    • Date
    • Status (Preliminary or Forecast or Final)

    I want to filter on status, only keeping the highest ranking one on a particular date, but for both names e.g.

    A   1/1/17 Preliminary

    B   1/1/17 Preliminary

    A   1/1/17 Final

    B   1/1/17 Final

    A   1/2/17 Preliminary

    B   1/2/17 Preliminary

    filtered to give


    A   1/1/17 Final

    B   1/1/17 Final

    A   1/2/17 Preliminary

    B   1/2/17 Preliminary

    How can I do this?

    Tuesday, February 11, 2020 4:25 PM

Answers

  • Hi,

    For your example you may use this code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group = Table.Group(Source, {"Name", "Status"}, {"Date", each List.Max([Date])})
    in
        group

    If there are other columns, you want to keep, use following code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group = Table.Group(Source, {"Name", "Status"}, {"Date", each List.Max([Date])}),
        cols = Table.ColumnNames(group),
        join = Table.Join(Source,cols,group,cols)
    in
        join

    If you also need to keep the order of rows, you should add index column before grouping, then sort it.

    Tuesday, February 11, 2020 4:42 PM