Forum Discussion

norocdinu's avatar
norocdinu
Frequent Visitor
3 years ago
Solved

Looping between 2 rows and create a 3rd row

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...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

rohit_singh's avatar
rohit_singh
Solution Sage
3 years ago

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's avatar
norocdinu
Frequent Visitor
3 years ago

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.