Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Many thanks for your help.
Alex
Solved! Go to 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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |