Forum Discussion
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_DanielCommunity Champion
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Prices1.Date] = List.Max(#"Changed Type"[Prices1.Date]))
- Vijay_A_VermaMost Valuable Professional
Replace #date(2022, 6, 23) with
Date.From(DateTime.FixedLocalNow())
- sm88483Frequent 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.
- Vijay_A_VermaMost Valuable Professional
Please go ahead with wdx223_Daniel 's solution.