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 ,
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
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.