Forum Discussion

adelmonte's avatar
adelmonte
Icon for Resolver I rankResolver I
3 years ago
Solved

Count of ID Updates

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 Spreadsh...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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's avatar
    adelmonte
    Icon for Resolver I rankResolver I
    3 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      3 years ago

      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.