Forum Discussion
Return Duplicates when Criteria Matched
Alright, hopefully someone comes in with a better solution that knows M better than I do. (It 100% works, but isn't very elegant).
This is all in Power Query, and you might want to change the order of things, mix and match steps, create an additional table before you start and make all the changes to the additional table so you don't lose any rows when you're all done, but you'll get the general idea if you read through the whole thing. All the pieces are here, and if you read it and understand it, you'll be able to apply it to your situation and probably even improve on it.
1) Convert everything to "Text" data type
2) Duplicate the table
3) Add a new custom column with the value "1", and "Remove" empty from the Production Facility Column. (But you could remove empty from any of the columns with blanks. The general idea is just to filter the rows where the column you choose is blank).
4) Delete Tracking and Product Format columns
5) use "Group By" on the remaining columns, Product Name, Production Date, and Production Facility.
6) You'll now see that the row you want to KEEP has Count of 2
7) Filter the "Count" column on >= 2
8 ) Now you want to create a "Key" column in both THIS table, and the duplicate table you created.
It should just be a concatenation of Product Name, Production Date, and Production Facility.
9) Go to the "Duplicate" table, and "Merge" it with the first table on the "Key" value created in both tables.
Here's the result:
Now just remove the "Table" column and the "Key" column, convert the data types back to what you want (date, number etc), and you're all done:
Last step is cleanup like disabling the "Load" of the original table into your model.
As I said, you might want to change some things here or there, but this is the general strategy I would take.
GENERAL STRATEGY:
You're grouping on the 3 requirements, excluding rows where there are not duplicates, creating a key, then doing an inner join on the key to exclude the other rows from the original table.
The .pbix file is attached.
/// Mediocre Power BI Advice, but it's free ///
Solution Sage1) What do you mean by "Return" = as stated about "Duplicates" and "Numbers 4 and 5"?
2) Which of these columns count as "criteria", and do you mean exactly three or do you mean three or more?
What exactly is the goal here? Do you want to exclude rows from the table entirely if they don't fit your criteria? Are you trying to produce a visual with only the rows that match the criteria?
More information please!
/// Mediocre Power BI Advice... But it's free! ///
Hey Kpost,
Thanks for responding. To answer your questions:
1) 4 and 5 would be the highlighted tracking numbers, Return meaning that only line items that share the same Product Name, Production Date and Production Facility appear.
2) the Criteria would be that the Production Name, Production Date and Production Facility are the same. So, not going off of just one column.
The end goal would be to visualize product that both a vendor and end customer have had an issue with.
- kpost
Alright, hopefully someone comes in with a better solution that knows M better than I do. (It 100% works, but isn't very elegant).
This is all in Power Query, and you might want to change the order of things, mix and match steps, create an additional table before you start and make all the changes to the additional table so you don't lose any rows when you're all done, but you'll get the general idea if you read through the whole thing. All the pieces are here, and if you read it and understand it, you'll be able to apply it to your situation and probably even improve on it.
1) Convert everything to "Text" data type
2) Duplicate the table
3) Add a new custom column with the value "1", and "Remove" empty from the Production Facility Column. (But you could remove empty from any of the columns with blanks. The general idea is just to filter the rows where the column you choose is blank).
4) Delete Tracking and Product Format columns
5) use "Group By" on the remaining columns, Product Name, Production Date, and Production Facility.
6) You'll now see that the row you want to KEEP has Count of 2
7) Filter the "Count" column on >= 2
8 ) Now you want to create a "Key" column in both THIS table, and the duplicate table you created.
It should just be a concatenation of Product Name, Production Date, and Production Facility.
9) Go to the "Duplicate" table, and "Merge" it with the first table on the "Key" value created in both tables.
Here's the result:
Now just remove the "Table" column and the "Key" column, convert the data types back to what you want (date, number etc), and you're all done:
Last step is cleanup like disabling the "Load" of the original table into your model.
As I said, you might want to change some things here or there, but this is the general strategy I would take.
GENERAL STRATEGY:
You're grouping on the 3 requirements, excluding rows where there are not duplicates, creating a key, then doing an inner join on the key to exclude the other rows from the original table.
The .pbix file is attached.
/// Mediocre Power BI Advice, but it's free ///
Hello there,
Thank you, this definetly got me thinking in the right direction and I was able to accomplish my goal.