Forum Discussion
Helper IRemove rows which contains entries from another Table
Hello,
I'm trying to create a table visual that only contains approved Suppliers. There is another table with this approved list. However, because suppliers sometimes have slight variations in their names (Eg. HomeShop #244, HomeShop #155, etc), it won't be a perfect match. In my case, the approved list would just contain "HomeShop" as an entry.
I've been trying to figure out a way to show only the approved suppliers. Other PBI forum suggests to use the Merge feature, however, my tables do not have an ID. Also sometimes the suppliers might be in all caps, some only 1st word cap, etc.
What would be the best way to go about this? I'm not the most experienced with DAX either.
Thank you.
Eg. Table A:
Supplier
HomeShop #354
HOMESHOP #155
PerfectVendor
Airliner USA
FruitVendor
Table B:
Approved Supplier List
HomeShop
Airline
Result Table:
Supplier:
HomeShop #354
HOMESHOP #155
Airliner USA
Hi shinney ,
You can create a calculated column as below, please find the details in the attachment.
Approved supplier = VAR _match = FIRSTNONBLANK ( FILTER ( VALUES ( 'B'[Approved Supplier List] ), SEARCH ( 'B'[Approved Supplier List], 'A'[Supplier], 1, 0 ) ), 1 ) RETURN IF ( NOT(ISBLANK(_match)), 'A'[Supplier] )In addition, you can refer the content in the following link to get it.
DAX – “CONTAINSX” Revisited: What WAS the Match?
Best Regards
4 Replies
- Fowmy
Super User
shinney
You can use the Fuzzy Match technique in Power Query with Ignore Case while merging these two tables to achieve this. I have attached the file below my signature. You need to set the Similarity Threshold to 0.5 which can vary based on the kind of records you have, you need to adjust it accordingly. - AlexisOlson
You filter TableA[Supplier] with the rule that if you look through the column TableB[Approved] there must be at least one match where TableB[Approved] is a substring of TableA[Supplier].
To implement this, add a step where you filter TableA[Supplier]. This will generate a set with M code like
Table.SelectRows(#"Changed Type", each ([Supplier] = "xyz"))We want a more complex condition, so replace each (...) with a new rule to get
Table.SelectRows(#"Changed Type", (r) => List.MatchesAny(TableB[Approved], each Text.Contains(r[Supplier], _)))There's some nested evaluation context here making the syntax a bit odd if you aren't used to M functions but it should be clear what List.MatchesAny and Text.Contains are doing.
shinneyWould there be a way to do this without changing the original Suppliers table? I would need to still use my full dataset, as the "Approved" visual is only a few of many pages in my report. Any chance there's an equivalent for List.MatchesAny and Text.Contains with a new measure or new column?
Thanks
Hi shinney ,
You can create a calculated column as below, please find the details in the attachment.
Approved supplier = VAR _match = FIRSTNONBLANK ( FILTER ( VALUES ( 'B'[Approved Supplier List] ), SEARCH ( 'B'[Approved Supplier List], 'A'[Supplier], 1, 0 ) ), 1 ) RETURN IF ( NOT(ISBLANK(_match)), 'A'[Supplier] )In addition, you can refer the content in the following link to get it.
DAX – “CONTAINSX” Revisited: What WAS the Match?
Best Regards
