Looping between 2 rows and create a 3rd row

[norocdinu]

Please help.

I have a situation where I have two ID columns. first one is the Initial ID second is the new ID. Over time the new ID wil be moved in initial ID and be once again replaced with a new one. This may happen multipla times. I need to iterate between theese 2 columns until I get the final ID. As per example below the loop must finish when ID = New ID

if Id<>New ID then remember New ID  and find in column ID and compare it with New ID again... do this until Id=New ID

[v-yanjiang-msft · Accepted solution]

Hi @norocdinu ,

According to your description, It's easy to do in DAX with PATH function, create a calculated column.

Column =
PATHITEM ( PATH ( 'Table'[ID], 'Table'[New ID] ), 1, TEXT )

I shuffled the data order of the sample, get correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[norocdinu]

Thank you! This worked perfectly!

[rohit_singh]

Hi @norocdinu ,

You could try something like this. 
Please open a blank query--> Advanced editor-->Remove any existing code and copy and paste the below code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3BDQAhCETRXjh7QECBWoj9t7EYjWFvk5dJfgQ4O4tDg85ILBNWi7e3knXSq2enSh6Qjt6d6gN19qN3/3XHBr6Ylpi+mJWYVV0f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, NewID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"NewID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FinalID", each if [ID] = [NewID] then [NewID] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"FinalID"})
in
    #"Filled Up"

Input

Output

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

[norocdinu]

Thank you for your answer, but the problem is that data is not sorted like in my example, this is why I cannot use this solution. 
I am looking inot some Loop functions, but so far I couldn't find a suitable solution.