Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Prices1.Date] = List.Max(#"Changed Type"[Prices1.Date]))
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Prices1.Date] = List.Max(#"Changed Type"[Prices1.Date]))
Replace #date(2022, 6, 23) with
Date.From(DateTime.FixedLocalNow())
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.