Merge Tables with Wildcards

[bibbylen]

Hi All,

Been struggling with this problem for a few days now,

I need to merge two tables in PowerQuery, Data & Rules. 

Data has N columns (Lets say 4 for simplicity), Rules has the same N columns + 1 more (RuleNo). I'd like to merge RuleNo onto the Data table where all columns match, but allow for columns in Rules to include a * as a wildcard. An example of the inputs/expected output below.

Data

A	B	C	D
1	2	3	4
1	3	3	5
1	3	3	6
1	4	2	3

Rules

RuleNo	A	B	C	D
One	1	2	3	4
Two	1	3	3	*

Output

A	B	C	D	RuleNo
1	2	3	4	One
1	3	3	5	Two
1	3	3	6	Two
1	4	2	3	null

There shouldn't in practise be any rows in Data that satisfy more than one row in Rules so shouldn't need to build in anything to stop this. Is this possible?

Thanks in advance.

[AlienSx]

Hi, @bibbylen 

let
    data_tbl = your_data_table,
    rules = List.Buffer(Table.ToRows(your_rules_table)),
    data = List.Buffer(Table.ToRows(data_tbl)),
    fx_compare = (r as list, d as list, w_card as text) =>
        [a = List.Buffer(List.Zip({List.Skip(r), d})),
        b = List.Select(a, List.IsDistinct),
        c = List.Count(b) <= 1 and (b{0}?{0}? ?? w_card) = w_card][c],
    txform = 
        List.Transform(
            data,
            (x) => x & {try rules{List.PositionOf(rules, x, Occurrence.First, (a, b) => fx_compare(a, b, "*"))}{0} otherwise null}
        ),
    to_tbl = Table.FromRows(txform, Table.ColumnNames(data_tbl) & {"RuleNo"})
in
    to_tbl

[bibbylen]

Hi Alien,

This seems to work for a single column to hold a WildCard, but more than one returns null. Is it possible to modify this to allow more than one WildCard?

Thanks

[AlienSx]

let
    data_tbl = your_data_table,
    rules = List.Buffer(Table.ToRows(your_rules_table)),
    data = List.Buffer(Table.ToRows(data_tbl)),
    fx_compare = (r as list, d as list, w_card as text) =>
        [a = List.Buffer(List.Zip({List.Skip(r), d})),
        b = List.Select(a, List.IsDistinct),
        c = List.AllTrue(List.Transform(b, (x) => List.Contains(x, w_card)))][c],
    txform = 
        List.Transform(
            data,
            (x) => x & {try rules{List.PositionOf(rules, x, Occurrence.First, (a, b) => fx_compare(a, b, "*"))}{0} otherwise null}
        ),
    to_tbl = Table.FromRows(txform, Table.ColumnNames(data_tbl) & {"RuleNo"})
in
    to_tbl

[tackytechtom]

Hi @bibbylen ,

How about something like this:

Here the data table (no changes done to it except to have integers as data types):

And here the rules table before:

And after:

As you can see, the idea was to create all the rows that the wildcard could potentially "contain". Since in your example we just have numbers, this was, in fact, quite easy and programmatically doable. Whether this works in your real life example, I am not sure. In case there is a finite number of values that the wildcard contains, you could also join in the master table to artificially create all the rows in the rules table. After that it is actually quite easy by merging the data and the rules tables with each other. Here the M code for the rules table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s9LVdJRMgRiIyA2BmITpVidaKWQ8nyouDEUaynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RuleNo = _t, A = _t, B = _t, C = _t, D = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"D", "D Orig"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "D Copy", each [D Orig]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","*","0",Replacer.ReplaceText,{"D Orig"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","*","9",Replacer.ReplaceText,{"D Copy"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value1", "D", each { Number.From ( [D Orig] ) ..Number.From ( [D Copy] ) }),
    #"Expanded D" = Table.ExpandListColumn(#"Added Custom1", "D"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded D",{"D Orig", "D Copy"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"RuleNo", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}})
in
    #"Changed Type"

By the way, ypur example could also have been solved by just joining on the columns A, B and C. But I am pretty sure this would not work for your real life example.

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[bibbylen]

I quite like the simplicity of this as an answer (Especially since I can understand the code!), May not be able to use it exactly as you've defined but maybe able to use the same idea of replacing the wildcard rules! Thanks!