I am new with Power BI and DAX, and hope the community can help with this specific issue.
The purpose is to identify potential duplicate invoices, which are registrered under a different invoice number (eg. Inv123 scanned again as Inv 123 or Inv.123).
As this is part of a larger analysis, I would prefer it could be as a Column, rather than using Power Query. The "Vendor Number", "Posting Date" and "Amount", would all be the same. Does anyone have an idea on how to mark these rows with a "Yes", and all others with a "No", or something similar?
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?
Well, in PQ you've got something like Merge Queries. You could therefore merge the table with a copy of itself (it must be a copy, a reference will not work) and the merge would be LEFT OUTER JOIN on the three columns you want. For all rows in the left table you'd get matches (in the form of a Table stored in the cell - you have to expand it). Then what happens is that some rows in the left table will be duplicated at least 2 times and some will be left alone (where there's only one match - the row itself). Having this you can remove the column(s) from the right table and do a groupby on the rows that were left. The groupby should use the Count(*) function. If you do that each row will have a count of duplicates. Now you can add a column and say: if count > 1 then "yes" else "no".
Easy? Can you follow yourself or should I create a dummy file with all the steps inside and post a link to it? But remember that I'm at work, so it might be faster if you try to implement this yourself