Forum Discussion
Power Query - Keep Rows based upon maximum date?
Hi -
I know there is a command in Power Query to 'keep rows', but is there a way to keep specific rows based upon the maximum date in the table ?
Example - transactiontable - start
| Date | Transaction ID |
| 11/8/2025 | ABC |
| 10/31/2025 | DEF |
| 11/8/2025 | GHI |
| 1/2/2025 | JKL |
Example - transactiontable - end result
| Date | Transaction ID |
| 11/8/2025 | ABC |
| 11/8/2025 | GHI |
I appreciate the help - thanks - jerryr
Hey jerryr0125 ,
Use the following M code that should full fill your requirements:
let
Source = transactiontable,
MaxDate = List.Max(Source[Date]),
FilteredRows = Table.SelectRows(Source, each [Date] = MaxDate)
in
FilteredRowsResult:
Also check the attached pbix file.
Best Regards,
Nasif Azam
9 Replies
Hey jerryr0125 ,
Use the following M code that should full fill your requirements:
let
Source = transactiontable,
MaxDate = List.Max(Source[Date]),
FilteredRows = Table.SelectRows(Source, each [Date] = MaxDate)
in
FilteredRowsResult:
Also check the attached pbix file.
Best Regards,
Nasif AzamHi - thank you for sharing - appreciate it!
what if I have a situation like this:
Table in Power Query (data) - Input
Department Transaction ID Date A 33 01/24/2025 A 44 03/01/2025 B 77 06/01/2025 B 88 07/01/2025 B 33 03/01/2025
Table in Power Query (data) - OutputDepartment Transaction ID Date A 44 03/01/2025 B 88 07/01/2025
I would like th create the table so that the latest data appears for each department . Any thoughts ? Thanks - JerryTable.ExpandRecordColumn( Table.Group( Source, "Department", {"x", (x) => Table.Max(x, "Date")} ), "x", {"Transaction ID", "Date"} )
Hi adding a step like Table.SelectRows(<your previous step>, each [Date] < Date.From("12/8/2025")) should work.
Hi,
if you wanted to tweak your M code in Query Editor, use below M code so that it gives you the result you expected.
let
// previous steps...
#"LastStep" = ...,// add these two lines:
MaxDate = List.Max(#"LastStep"[Date]),
#"MaxDateRows" = Table.SelectRows(#"LastStep", each [Date] = MaxDate)
in
#"MaxDateRows"Hi jerryr0125
Just checking in to see if the previous responses helped resolve your issue. If not, feel free to share your questions and we’ll be glad to assist.
Hi jerryr0125
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hi - none of these answers produce the outcome required - any thoughts ?
Hi jerryr125
Thanks for the update. Since none of the suggested approaches produced the expected outcome, could you please share a small sample of the data (with any sensitive information removed) along with the expected result? This will help us better understand the scenario and provide a more accurate solution.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
