Forum Discussion
Identify rows with duplicate values
Look:
Here's the M code:
let Source = Invoices,
#"Merged Queries" = Table.NestedJoin(Source, {"Vendor", "Posting Date", "Amount"}, #"Invoices - Duplicate Invoice Marker", {"Vendor", "Posting Date", "Amount"}, "Invoices - Duplicate Invoice Marker", JoinKind.LeftOuter), #"Expanded Invoices - Duplicate Invoice Marker" = Table.ExpandTableColumn(#"Merged Queries", "Invoices - Duplicate Invoice Marker", {"Inv No"}, {"Inv No.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Invoices - Duplicate Invoice Marker",{"Inv No.1"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Inv No", "Vendor", "Posting Date", "Amount"}, {{"Count", each Table.RowCount(_), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Has Dups", each if [Count] > 1 then "Yes" else "No"), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}) in #"Removed Columns2"Here are the table dependencies:
Best
Darek
Why don't you do this in Power Query where it really should be done? Such calculations belong to Power Query, not DAX, even though, of course, it's possible to do it in DAX.
Best
Darek
Thanks for taking your time reviewing my problem!
I don't mind using Power Query, but having the specific rows marked, while still retaining all the data, would be greatly preferred. I have a Data Model with many dimension tables and measures, so if at all possible, it would be benificial to just add a new column with "yes" and "no", where "yes" marks the rows which are suspected as duplicates.
I tried an If statement in a custom column in power query, but all results are positive:
if [Vendornumber] = [Vendornumber] and
[Amount] = [Amount] and
[Date] = [Date]
then "Yes"
else "No"
How would you mark only the rows where alle three criteria are true?
Regards
Jonas
Well, mate, if you think carefully about what you've done in the IF statement, it'll be no surprise that you got "YES" only. IS 1 = 1? IS a = a? Is oaesuthtteasouhe = oaesuthtteasouhe?
Whatever you do - THINK.
To do what you want in PQ, requires thinking in M, not in DAX.
Best
Darek
My core issue is how to mark identical rows based on three criteria?
I understand why the If statement returns all positives, but thought it would help clarify my conundrum with the best example I could conjure up.
After hours of searching, I didn't come across any posted problems/solutions. I decided to describe my problem here as best possible, even though it is simple stuff for advanced users.
I get what you are saying mate, but when you hit that wall of knowledge, and google doesn’t do the trick, you need help, maybe even to formulate your problem..
Look:
Here's the M code:
let Source = Invoices,
#"Merged Queries" = Table.NestedJoin(Source, {"Vendor", "Posting Date", "Amount"}, #"Invoices - Duplicate Invoice Marker", {"Vendor", "Posting Date", "Amount"}, "Invoices - Duplicate Invoice Marker", JoinKind.LeftOuter), #"Expanded Invoices - Duplicate Invoice Marker" = Table.ExpandTableColumn(#"Merged Queries", "Invoices - Duplicate Invoice Marker", {"Inv No"}, {"Inv No.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Invoices - Duplicate Invoice Marker",{"Inv No.1"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Inv No", "Vendor", "Posting Date", "Amount"}, {{"Count", each Table.RowCount(_), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Has Dups", each if [Count] > 1 then "Yes" else "No"), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}) in #"Removed Columns2"Here are the table dependencies:
Best
Darek