Forum Discussion
Latest Item in a replace by chain
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
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