cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daniel_L Occasional Visitor
Occasional Visitor

Merge with wildcards

Hello Power BI Community,

I need to find (in other words set a flag for) the rows in table A which matches any of the rows in Table B. If it wasn't for the wildcards (*) it would have been easy to create a combined key in each table and merge the tables. (Wildcard means the rule is valid for any value in that column.)
How can I achieve this?

Table A: Transactions, 100000+ rows

CompanyDivDeptInvoice
22A1321908711058256
22C22310204211264977
22B22334074211274926
22A22336154211275016
22D22336154213075816
22A212086004213075886
22A3877004213075886
22A311294213076187
22C231008004513002378
24B211014064513002382
24A16212051803336279
24D224080006621870
24A35100126625197
24A13218076626487
24A225030006627186
24C224070006627635


Table B: Rules, ~50 rows

Company2Div2Dept2Invoice2
*A162120*
24D**
*A3510012*
*A1321807*
*A22503000*
24C**
22**711058256
22C22310204211264977
22B2233407*
**2233615*
*D2233615*
**21208600*
**387700*

 

Best Regards,
Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Merge with wildcards

@Daniel_L,

 

You may use code below to add a custom column.

let
    r = _
in
    Table.MatchesAnyRows(
        Rules,
        each List.Contains({r[Company], "*"}, [Company2])
        and List.Contains({r[Div], "*"}, [Div2])
        and List.Contains({r[Dept], "*"}, [Dept2])
        and List.Contains({r[Invoice], "*"}, [Invoice2])
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Merge with wildcards

@Daniel_L,

 

You may use code below to add a custom column.

let
    r = _
in
    Table.MatchesAnyRows(
        Rules,
        each List.Contains({r[Company], "*"}, [Company2])
        and List.Contains({r[Div], "*"}, [Div2])
        and List.Contains({r[Dept], "*"}, [Dept2])
        and List.Contains({r[Invoice], "*"}, [Invoice2])
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.