Forum Discussion
Solved
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 Tra...
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
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
FilteredRows
Result:
Also check the attached pbix file.
Best Regards,
Nasif Azam
Hi - 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"} )
