Tracking variable name changes within group in Power BI

Question

I am annually tracking the names of car models, which may or may not change each year (its random).

Suppose I start with three models in 2023 that all changes name in 2024 and 2025, thus producing the columns "Year", "Old_name", and "New_name" in the dataset below. E.g., a model is renamed from "H" to "I" in 2024, then renamed from "I" to "J" in 2025, and finally from "J" to "K" in 2025 ("H" through "K" is thus the same model).

I would like to produce a calculated column ("Naming_group") that tracks the change over time, and then gives it the newest name that the model currently has. E.g., "K" is returned for the model that was once called "H", "I", and "J".

My dataset is fairly small, so this is the only table that is in the dashboard.

Unfortunately, I do not have much DAX code to show that I have tried to solve it myself up to this point.

Year	Old_name	New_name	Naming_group
2023	A	A	C
2023	H	I	K
2023	V	X	Z
2024	A	B	C
2024	I	J	K
2024	X	Y	Z
2025	B	C	C
2025	J	K	K
2025	Y	Z	Z

Thank you in advance!

Edit: it should be noted that a model does not necessarily change its name from year to year.

Answer 1

updated the answer

1. try to use PQ to create a new table

    let
   
        Source = Table.SelectRows(Table,each [Old_name]<>[New_name]),
   
        #"Removed Columns" = Table.RemoveColumns(Source,{"Year"}),
   
        #"Appended Query" = Table.Combine({#"Removed Columns",Table.RenameColumns(Table.FromList(Table.SelectRows(Table,each [Year]=List.Max(Table[Year]))[New_name]),{{"Column1","Old_name"}})})
   
    in
   
        #"Appended Query"
   

2. use DAX to create columns in the new table

   Column = path('Table (2)'[Old_name],'Table (2)'[New_name])

   Column 2 = left([Column],1)

3. create a new column in your original table

   group = MAXX ( FILTER ( 'Table (2)', 'Table (2)'[Old_name] = 'Table'[Old_name] ), [Column 2] )