Latest Item in a replace by chain

[FRV]

Hi all,

I really need some help as I am struggling to get to get what I need. I want to find the latest item in a replace by chain. I tried doing it with a calculated column but that doesn't work as I need to loop through rows and find the latest item in the chain. I think it can be done with Power Query but I fail to get it right. The source is an Excel file. My table has two text columns which are Item and Replace by. Below an example of the table and the Latest column that I want to add:

Item	Replace by	Latest
A	B	D
B	C	D
C	D	D
D		D
E		E
F	Z	F
G	G	G

The idea is that for each row it needs to check the replace by item. Check that item in Item and find the replace by value and do this until there is no item in replace by, the item in replace by can't be found in Item or Replace by equals Item. I tried everything and even tried AI to help me but I keep failing.

I constantly get column name errors although they are equal and I am just lost so I am just pasting my basic M code:

let
    Source = Excel.Workbook(File.Contents("link to my file"), null, true),
    ReplaceBy_Sheet = Source{[Item="ReplaceBy",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(ReplaceBy_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", type text}, {"Replace By", type text}})
in
    #"Changed Type"

If someone can help me out you really made my day!

[Direct]

Hi @FRV 

To achieve this in Power Query, the approach begins by converting the table into a list of records using Table.ToRecords. This allows easy lookup of each record based on its item value, which is essential for implementing iterative or recursive logic.

A recursive function called GetLatest is then defined. For each item, this function checks its corresponding "Replace by" value. If a next item is found and valid, the function calls itself repeatedly until it reaches the final item in the chain — that is, when "Replace by" is empty, null, or the same as the item itself.

Finally, a new column is added to the table using Table.AddColumn, applying this function row by row to generate the "Latest" value for each item.

This approach is robust because it handles chains of any length, prevents infinite loops by checking stop conditions, and avoids the need for multiple merges or complex joins. It also keeps the logic clear and maintainable directly within Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWJBpI6Ss5gljOQ5QJmuQBZYIYrjOEGZESBWe5AlrtSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Replace by" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Replace by", type text}}),
    Records = Table.ToRecords(Source),

    GetLatest = (startItem as text) as text => 
    let
        nextItemRecord = List.First(List.Select(Records, each _[Item] = startItem), null),
        nextItem = if nextItemRecord = null then null else nextItemRecord[Replace by],
        result = if nextItem = null or nextItem = "" or nextItem = startItem then
                    startItem
                 else
                    @GetLatest(nextItem)
    in
        result,

    AddLatest = Table.AddColumn(Source, "Latest", each GetLatest([Item]), type text)
in
    AddLatest

[speedramps · Accepted solution]

Hi   @FRV

Try this easy method ...

Clean your data and add the mssing rows and column, otherwise you will get an error message

Add a calculated DAX column  (not Power or a measure) It must be calculated column 

Path = PATH( yourdata[Item], yourdata[Replace by] ) 

Note that the first item in the path is the answer you want

Create another calculated colum to retrive the answer  (it must be calculated column and not a measure)

Answer = PATHITEM(yourdata[Path],1)

Please click thumbs up because I have tried to help.

Then click accept solution if it works (you can see that that it does work).

Learn more about the PATH function here https://www.youtube.com/watch?v=EzfLJFEKV8I

[FRV]

I am stunned... It's this easy. I was caught in an endless loop of difficult solutions and couldn't make it work in a single calculated column. Also didn't know about the PATH function. Many thanks for your help!!! You saved my life.

[speedramps]

Thank you @FRV 

If you give a person a fish, you just give them a meal.

If you teach them to fish, then they can feed themselves and teach their friends and family.

Incidentally, well done on asking the question clearly and concisely with example input data and desired output.

I wish everyone did that !

[rohit1991]

Hi @FRV ,

It sounds like you're trying to resolve a recursive "replace by" chain in Power Query to determine the final/latest item in the sequence.

This is a common scenario when dealing with product lifecycle or substitution chains. Since Power Query (M) doesn't support true recursion natively, we'll simulate it using a custom function that loops through replacements until it reaches the end of the chain. Here's a way to solve your problem using a custom function and a merge operation:

let
// Load and clean your data
Source = Excel.Workbook(File.Contents("link to your file"), null, true),
ReplaceBy_Sheet = Source{[Item="ReplaceBy",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(ReplaceBy_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", type text}, {"Replace By", type text}}),

// Create a function to trace the replacement chain
GetLatest = (startItem as text, table as table) as text =>
let
nextItem = Record.FieldOrDefault(
Table.SelectRows(table, each [Item] = startItem){0}?,
"Replace By",
null
),
result = if nextItem = null or nextItem = "" or nextItem = startItem
then startItem
else @GetLatest(nextItem, table)
in
result,

// Add a column to compute the Latest item using the function
AddLatestColumn = Table.AddColumn(#"Changed Type", "Latest", each GetLatest([Item], #"Changed Type"), type text)

in
AddLatestColumn

[FRV]

Many many thanks!! This is the second working solution. Since the other was earlier I accepted that as solution. But this also works really well and has less steps. The only downside but that's for both solutions is that when an Item in Replace By doesn't exist in Item (this happens due to manual input errors) It will still take this as the Latest. I will remove that manually in the input file.