Merge Tables with Wildcards

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/

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