The last date

[ElinG]

Hi

I have a column Org_parner_nr that is not unik, and then I have like snap_month that I will take out the last one

Is there anyone that know how to?

[Cookistador · Accepted solution]

Hello @ElinG 

You can solve this issue with the following step

1)Create a copy of your table and name it for FilterDate (or the name you want to give)

2) Delete all columns excpept snap_month 

3) Select the column then Transform --> Date --> Latest

you should have something like this 

4) In your intitial table, Applied a filter on the date (select one date in your list)
You will get something like:

= Table.SelectRows(#"Changed Type", each ([snap_month ] = #date(2025, 2, 15)))
5) Replace date(2025, 2, 15) by FilterDate (or the name you gave to your table)

Tadam, you only get the last value, each time you will refresh, your filteredtable will be updated

If it is not what you are trying to achieve, let me know 🙂

[v-veshwara-msft]

Hi @ElinG ,

We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

If you're still experiencing issues, please let us know.
Thank you.

[v-veshwara-msft]

Hi @ElinG ,

Just checking in again as we haven’t heard back from you. If the provided solutions addressed your issue, kindly mark the helpful reply as the Accepted Solution to assist others with similar queries.

If your issue is still unresolved or you need further help, feel free to reach out.

Thank you.

[rohit1991 · Accepted solution]

Hi @ElinG ,

If you have a column like Org_partner_nr (not unique) and a snap_month column (representing a time snapshot), and you want to keep only the latest snap_month for each Org_partner_nr, here’s how you can do it in Power Query:

1. Sort the table by snap_month in descending order.

2. Then go to the "Home" tab and use "Group By".

3. In the Group By window: Group by: Org_partner_nr >> Operation: Select "All Rows" (this will nest the grouped data).

4. Add a custom column inside the nested tables to extract the top row (the latest one).

5. Expand the nested tables to bring the full row back.

Or alternatively, if you're comfortable with code, you can use something like this in M:

let
    SortedTable = Table.Sort(YourTable, {{"Org_partner_nr", Order.Ascending}, {"snap_month", Order.Descending}}),
    GroupedTable = Table.Group(SortedTable, {"Org_partner_nr"}, {{"Latest", each Table.FirstN(_, 1)}}),
    ExpandedTable = Table.ExpandTableColumn(GroupedTable, "Latest", Table.ColumnNames(SortedTable))
in
    ExpandedTable

[v-veshwara-msft]

Hi @ElinG ,
Just following up to check if the issue is resolved and the solution provided by @Cookistador met your needs. If yes, could you please consider marking the helpful reply as "Accepted Solution" to assist others with similar queries. If further assistance is needed, please reach out.
Thanks @Cookistador for your response.

Thank you.

[Cookistador · Accepted solution]

Hello @ElinG 

You can solve this issue with the following step

1)Create a copy of your table and name it for FilterDate (or the name you want to give)

2) Delete all columns excpept snap_month 

3) Select the column then Transform --> Date --> Latest

you should have something like this 

4) In your intitial table, Applied a filter on the date (select one date in your list)
You will get something like:

= Table.SelectRows(#"Changed Type", each ([snap_month ] = #date(2025, 2, 15)))
5) Replace date(2025, 2, 15) by FilterDate (or the name you gave to your table)

Tadam, you only get the last value, each time you will refresh, your filteredtable will be updated

If it is not what you are trying to achieve, let me know 🙂