Forum Discussion

Jug's avatar
Jug
Frequent Visitor
3 years ago

Select rows with the most recent date without groupby

Hi, I would like to know if someone could help me with the following question:   I need to filter only the columns with the most recent date (and smaller than today, if possible), filtering the max...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Address", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] < DateTime.Date(DateTime.LocalNow())),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID"}, {{"Count", each Table.Max(_,"Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Date", "Address"}, {"Date", "Address"})
in
    #"Expanded Count"

Hope this helps.

Jug's avatar
Jug
Frequent Visitor
3 years ago

I replaced all the field names with the real ones in the table correctly, the problem is that after the "IN" if I put only  #"Expanded Count" it does not work, I believe it needs to mention the table too

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    After in, you always have to specify the name of the last step.

    • Jug's avatar
      Jug
      Frequent Visitor
      3 years ago

      Unfortunately, it does not work because it has a group function and this is not supported with DirectQuery.