Count of ID Updates

[adelmonte]

Hi all,

I need to figure out how many times there were changes starting from the Initial ID

End result should be in this example below the Initial ID 6716634 changed 4 times.

Google Spreadsheet

Many thanks for your help.

Alex

[lbendlin]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Step 1:  Correct your source data as a couple of Old IDs are missing from the Current ID list.

6715687
6721494
6723089
6727961
6728120
6738710
6750036
6751305
6751414
6752705
6764652
6769456
6769674
6774626

Second step:  Create a path calculated column

Column = path(Data[Current ID],Data[Old ID])

Step 3 - Number of changes equates to PATHLENGTH minus one.

Changes = pathlength([Column])-1

See attached 

[adelmonte]

Hi @lbendlin,
Thank you so much for your help so far.
I can't correct the data as this is the real situation of the data and I should be able to handle these situations in future also 😞
Is there a way to manage this? Excluding somehow those that don't match.

Thanks,

Alex

[lbendlin · Accepted solution]

You can include the replenishment as part of the Power Query.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\Count of ID Updates.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    Combined = #"Promoted Headers" & Table.FromList(List.Distinct(List.Difference(#"Promoted Headers"[Old ID],#"Promoted Headers"[Current ID])), Splitter.SplitByNothing(), {"Current ID"}, null, ExtraValues.Error),
    #"Filtered Rows" = Table.SelectRows(Combined, each ([Current ID] <> null))
in
    #"Filtered Rows"

see attached.