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


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.

4 Replies

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    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.