Forum Discussion
Look for a Way to Display Duplicate Line Items for Auditing
Hello all,
I'm looking to use PBI to audit duplicate entries for payment reimbursements. Say I have a table with 3 columns, "Employee", "Line Item", "Amount". I have figured out how to search the "Amount" column for duplicates and created a 4th column that displays "Duplicate" or "Unique". I have a filter set up on my visual to only show the duplicate amounts. What I'm having trouble with is narrowing in filtering on those duplicate amounts to only show items that are truely duplicates for a particular individual and line item.
For example here is my visual:
| Employee | Line Item | Amount | Duplicates |
| Bob Smith | Airfare | 500.00 | Duplicate |
| Renee Jones | Car Rental | 300.00 | Duplicate |
| Bob Smith | Airfare | 400.00 | Unique |
| Renee Jones | Airfare | 500.00 | Duplicate |
| Renee Jones | Car Rental | 300.00 | Duplicate |
| Bob Smith | Car Rental | 300.00 | Duplicate |
So even though there are a few duplicate amounts, the only true duplicate entry would be Renee Jones Car Rental for 300.00. What would I need to do to have this as a result:
| Employee | Line Item | Amount | Duplicates |
| Renee Jones | Car Rental | 300.00 | Duplicate |
Thanks for any guidance.
Do a group by with all the columns being key columns. Add the aggregate of Count. Then filter on Count > 1.
2 Replies
Do a group by with all the columns being key columns. Add the aggregate of Count. Then filter on Count > 1.
Thank you!