Merge with wildcards

[Daniel_L]

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

Company	Div	Dept	Invoice
22	A	1321908	711058256
22	C	2231020	4211264977
22	B	2233407	4211274926
22	A	2233615	4211275016
22	D	2233615	4213075816
22	A	21208600	4213075886
22	A	387700	4213075886
22	A	31129	4213076187
22	C	23100800	4513002378
24	B	21101406	4513002382
24	A	162120	51803336279
24	D	22408000	6621870
24	A	3510012	6625197
24	A	1321807	6626487
24	A	22503000	6627186
24	C	22407000	6627635

Table B: Rules, ~50 rows

Company2	Div2	Dept2	Invoice2
*	A	162120	*
24	D	*	*
*	A	3510012	*
*	A	1321807	*
*	A	22503000	*
24	C	*	*
22	*	*	711058256
22	C	2231020	4211264977
22	B	2233407	*
*	*	2233615	*
*	D	2233615	*
*	*	21208600	*
*	*	387700	*

Best Regards,
Daniel

[v-chuncz-msft · Accepted solution]

@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])
    )

[SergioBorges]

And is there any way to list the line (lines) that matched instead of true or false?