Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Power Query Merge based on conditions

Hi everyone,   I am trying to merge two tables, and I think I need to do the merge based on conditions.  The first table is a list of all finished inventory, which includes the end customer name, c...
AlienSx's avatar
AlienSx
Super User
2 years ago

Hello, Barbara. I am not sure about performance but give this a try

let
    customer = your_customers_table,
    inventory = your_inventory_table,
    cust_group = Table.Group(customer, {"Customer"}, {{"all", Table.ToRecords}}),
    cust_dict = Record.FromList(cust_group[all], cust_group[Customer]),
    fx_select = (dict, rec) =>
        [a = Record.FieldOrDefault(dict, rec[Customer], null),
        sel = if a = null then null else 
            List.Select(
                a, 
                (x) => 
                    (x[City] = "all" or x[City] = rec[City]) and 
                    (x[State] = "all" or x[State] = rec[State])
            ){0}?[Warehouse]?][sel],
    wh = Table.AddColumn(inventory, "Warehouse", (x) => fx_select(cust_dict, x))
in
    wh