Forum Discussion

sm88483's avatar
sm88483
Frequent Visitor
4 years ago
Solved

How to automatically filter for max value

I want power query to automatically filter for the most recent date in the date column.

 

The current code is shown below, in the last line of code there is the date field which is manually input, how do I alter it so that it automatically takes the most recent date? Apologies if this is a really simple question. Thanks in advance.

 

let
Source = Sql.Databases("source.com"),
SecDBTest = Source{[Name="SecDBTest"]}[Data],
dbo_Securities2 = SecDBTest{[Schema="dbo",Item="Securities2"]}[Data],
#"Merged Queries" = Table.NestedJoin(dbo_Securities2, {"Security ID"}, Prices1, {"Security"}, "Prices1", JoinKind.LeftOuter),
#"Expanded Prices1" = Table.ExpandTableColumn(#"Merged Queries", "Prices1", {"Date", "Market Price "}, {"Prices1.Date", "Prices1.Market Price "}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Prices1",{{"Prices1.Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Prices1.Date] = #date(2022, 6, 23)))
in
#"Filtered Rows"
  • #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Prices1.Date] = List.Max(#"Changed Type"[Prices1.Date]))

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Prices1.Date] = List.Max(#"Changed Type"[Prices1.Date]))

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional

    Replace #date(2022, 6, 23) with

    Date.From(DateTime.FixedLocalNow())

    • sm88483's avatar
      sm88483
      Frequent Visitor

      Thanks for the response. Would that just be filtering for today's date? If so, today's date may not be actually in the dataset yet, I would need it to look at the column and find the largest date that is contained in the dataset.