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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 406 members 3,275 guests
Please welcome our newest community members: