How to join two tables when one table has filters to applied to the second one?

Question

I have these two tables:

+-------------------+  +-------------------+
|        TAB1       |  |        TAB2       |
+---------+---------+  +---------+---------+
| PRODUCT | ACCOUNT |  |   GR    |   MASK  |
+---------+---------+  +---------+---------+
| apple   |  1001   |  | fruits  |  1%     |
| banana  |  1002   |  | cars    |  _2%    |
| bike    |  2101   |  | bikes   |  21%    |
| car     |  2202   |  | other   |  2[^12] |
| tree    |  2401   |  | spec    |  2%     |
| pool    |  2502   |  +---------+---------+
+---------+---------+

I need a resulting table which looks like this:

+-----------------------------+
|          TABRES             |
+---------+-------------------+
|   GR    | PRODUCT | ACCOUNT |
+---------+---------+---------+
| fruits  | apple   |  1001   |
| fruits  | banana  |  1002   |
| cars    | car     |  2202   |
| bikes   | bike    |  2101   |
| other   | tree    |  2401   |
| other   | pool    |  2502   |
| spec    | bike    |  2101   |
| spec    | car     |  2202   |
| spec    | tree    |  2401   |
| spec    | pool    |  2502   |
+---------+---------+---------+

The first table (TAB1) stores information about products and accounts related to those products.

The second table (TAB2) stores information about groups and related account masks.

I need to connect groups with the products.

There can be more than one group connected to a product ie. group "spec" will be related to products which belong to groups: cars, bikes, other.

I suppose that the key is to create an in-memory new table for each row in TAB2 as left join with filtering the TAB1 using a LIKE condition, where the value of parameter for LIKE is taken from a row of column MASK in table TAB2. At the end combined all results together and put it to one result table.

But I have no idea how to do it. I have tried it as a left join (blow) without any result.

I have tried in PowerQuery:

= Table.Join(tab2, {"   MASK  "}, tab1, {" ACCOUNT "}, JoinKind.LeftOuter)

But it doesn't work - this is what I get:

GR          MASK    PRODUCT          ACCOUNT 
fruits      1%      null             null
cars        _2%     null             null
bikes       21%     null             null
other       2[^12]  null             null
spec        2%      null             null

Neither M nor DAX supports the SQL Like expressions. Can you change the MASK or must the existing masks be translated into something understandable by M or DAX? — Ron Rosenfeld
– Ron Rosenfeld, Commented Aug 8 at 1:26

Michal · Accepted Answer · 2025-08-08 01:14:10Z

This one was fun to solve and I learnt something new! PowerQuery does not natively support REGEX (which can be used to match the MASK input) but I borrowed this JavaScript solution and it seems to be working:

let
    // Hardcoded TAB1
    TAB1 = Table.FromRows({
        {"apple", "1001"},
        {"banana", "1002"},
        {"bike", "2101"},
        {"car", "2202"},
        {"tree", "2401"},
        {"pool", "2502"}
    }, {"PRODUCT", "ACCOUNT"}),

    // Hardcoded TAB2 with regex-style MASK
    TAB2 = Table.FromRows({
        {"fruits", "^1"},
        {"cars", "^.2"},
        {"bikes", "^21"},
        {"other", "^2[^12]"},
        {"spec", "^2"}
    }, {"GR", "MASK"}),

    // Cross join using nested table method
    CrossJoinNested = Table.AddColumn(TAB2, "TAB1", each TAB1),
    CrossJoined = Table.ExpandTableColumn(CrossJoinNested, "TAB1", {"PRODUCT", "ACCOUNT"}),

    // Implementation of Text.RegexTest function
Text.RegexTest = (text as text, pattern as text, optional caseSensitivity as nullable number) =>
let
    caseSensitivityValue = if caseSensitivity = null then 0 else caseSensitivity,

    regexModifier = if caseSensitivityValue = 0 then "" else "i",

    // Construct the JavaScript regex expression
    regexExpression = "/" & pattern & "/" & regexModifier,
        
    // Create HTML with embedded JavaScript to evaluate the regex pattern
    htmlContent = 
        "<script>" &
        "var matches = """ & text & """.match(" & regexExpression & ");" &
        "document.write(matches ? 'true' : 'false');" &
        "</script>",

    resultText = Web.Page(htmlContent)[Data]{0}[Children]{0}[Children]{1}[Text]{0},
    resultValue = if resultText = "true" then true else false
in
    resultValue,


    // Add column to evaluate regex match
    WithMatch = Table.AddColumn(CrossJoined, "IsMatch", each Text.RegexTest([ACCOUNT], [MASK])),
    #"Filtered Rows" = Table.SelectRows(WithMatch, each true),

    // Filter matched rows
    Filtered = Table.SelectRows(#"Filtered Rows", each [IsMatch] = true),

    // Final result
    Result = Table.SelectColumns(Filtered, {"GR", "PRODUCT", "ACCOUNT"})
in
    Result

Jacek · Accepted Answer · 2025-08-13 06:30:52Z

0

Solution based on python:

let
    BufferedTAB1 = Table.Buffer(TAB1),
    BufferedTAB2 = Table.Buffer(TAB2),

    PythonStep = Python.Execute(
"
import pandas as pd
import re

# Import data from Power Query
tab1 = dataset1.copy()
tab2 = dataset2.copy()

results = pd.DataFrame()

# Cross join 
tab1['key'] = 1
tab2['key'] = 1
for _, row1 in tab1.iterrows():

    row1_df = pd.DataFrame([row1])
    merged = pd.merge(row1_df, tab2, on='key').drop('key', axis=1)

    mask = merged.apply(lambda row: bool(re.match(str(row['MASK']), str(row['ACCOUNT']))), axis=1)
    filtered = merged[mask]
    results = pd.concat([results, filtered], ignore_index=True)

",
        [
            dataset1 = BufferedTAB1,
            dataset2 = BufferedTAB2
        ]
    ),

    TAB3 = PythonStep{[Name="results"]}[Value],
    Result = Table.SelectColumns(TAB3, {"GR", "PRODUCT", "ACCOUNT"})
in
    Result

answered Aug 13 at 6:30

Jacek

311 silver badge3 bronze badges

1 Comment

Jeremy Caney Aug 13 at 6:43

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Collectives™ on Stack Overflow

How to join two tables when one table has filters to applied to the second one?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related