Forum Discussion
adelmonte
Resolver I
Resolver ICount 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...
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.
adelmonteResolver I
Resolver IHi 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
Super User
Super UserYou 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.