Forum Discussion

bibbylen's avatar
bibbylen
Regular Visitor
3 years ago

Merge Tables with Wildcards

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...
Need Help
AlienSx's avatar
AlienSx
Super User
3 years ago

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's avatar
bibbylen
Regular Visitor
3 years ago

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's avatar
    AlienSx
    Super User
    3 years ago
    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