Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sm88483
Frequent Visitor

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"
1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

Vijay_A_Verma
Super User
Super User

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.

Please go ahead with @wdx223_Daniel 's solution. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.