Forum Discussion
Filtering out old rows based on status in most recent row
I want to use "Group By" in Power Query, but I don't want to aggregate anything. A loan number can have several rows of old data in a table before it gets to the current row with the most recent status, and if the most recent row shows "PAIDINFULL", I want ALL the rows associated with that loan number filtered out. How can I group the loan numbers together by the most recent status?
(I'm loosely following this approach: https://community.fabric.microsoft.com/t5/Desktop/Filter-and-remove-data-in-Power-Query/m-p/2429554#M869436)
Sample Data:
| Loan | Status | Accounting Date |
| 1 | REPAYMENT | 4/30/2019 |
| 1 | REPAYMENT | 7/31/2020 |
| 1 | PAIDINFULL | 12/31/2024 |
| 2 | REPAYMENT | 6/30/2019 |
| 2 | REPAYMENT | 7/31/2019 |
| 2 | REPAYMENT | 1/31/2022 |
| 2 | PAIDINFULL | 7/31/2024 |
| 3 | REPAYMENT | 3/31/2020 |
| 3 | REPAYMENT | 8/31/2020 |
| 4 | REPAYMENT | 11/30/2021 |
| 4 | REPAYMENT | 6/30/2023 |
| 4 | REPAYMENT | 5/31/2024 |
Edit: for some reason my table looks like sh#t. here's a screenshot:
Thanks.
Anonymous,
Try this Power Query solution. The original query is named Raw.
Create query MaxDate:
let Source = Raw, GroupRows = Table.Group( Source, {"Loan"}, { {"MaxDate", each List.Max([Accounting Date]), type nullable date}, { "All", each _, type table [Loan = nullable number, Status = nullable text, Accounting Date = nullable date] } } ), ExpandTable = Table.ExpandTableColumn(GroupRows, "All", {"Status"}, {"Status"}), FilterPaidInFull = Table.SelectRows(ExpandTable, each ([Status] = "PAIDINFULL")) in FilterPaidInFullCreate query below. This query uses a left anti-join to exclude any loans that are in MaxDate (these are the PAIDINFULL loans). You can disable the load of queries Raw and MaxDate so only the final query will appear in your model.
let Source = Table.NestedJoin(Raw, {"Loan"}, MaxDate, {"Loan"}, "MaxDate", JoinKind.LeftAnti), RemoveMaxDate = Table.RemoveColumns(Source, {"MaxDate"}) in RemoveMaxDate
3 Replies
- DataInsights
Super User
Anonymous,
Try this Power Query solution. The original query is named Raw.
Create query MaxDate:
let Source = Raw, GroupRows = Table.Group( Source, {"Loan"}, { {"MaxDate", each List.Max([Accounting Date]), type nullable date}, { "All", each _, type table [Loan = nullable number, Status = nullable text, Accounting Date = nullable date] } } ), ExpandTable = Table.ExpandTableColumn(GroupRows, "All", {"Status"}, {"Status"}), FilterPaidInFull = Table.SelectRows(ExpandTable, each ([Status] = "PAIDINFULL")) in FilterPaidInFullCreate query below. This query uses a left anti-join to exclude any loans that are in MaxDate (these are the PAIDINFULL loans). You can disable the load of queries Raw and MaxDate so only the final query will appear in your model.
let Source = Table.NestedJoin(Raw, {"Loan"}, MaxDate, {"Loan"}, "MaxDate", JoinKind.LeftAnti), RemoveMaxDate = Table.RemoveColumns(Source, {"MaxDate"}) in RemoveMaxDate- DataInsights
Anonymous,
Correct, use "Reference".