cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daniel_L
Regular 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
v-chuncz-msft
Community Support
Community Support

@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.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors