Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
adelmonte
Resolver I
Resolver I

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.

adelmonte_0-1663942509739.png

Google Spreadsheet

Many thanks for your help.

Alex

 

 

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

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

lbendlin_0-1664063259022.png

See attached 

 

 

 

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

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.