Power query lookup within and if statement

[NSSLynne]

Is it possible to do a lookup with an if condition from one table to another in Power Query?

I have one table which has Customer & Site name for job(s), I have another table with Customers/sites listed with PO's.

I would like to say, if the PO table has a site name = to the site name in the Jobs table, return PO column value, if not lookup Customer Name and return what is in PO column value.

There are many Customers and Many sites in each table, I can't do it with merge....

[v-stephen-msft]

Hi @NSSLynne ,

You can use the Table.NestedJoin function to join the two tables based on the site name column. Then, you can use the if statement to check if the PO table has a site name equal to the site name in the Jobs table. If it does, return the PO column value. If not, lookup the Customer Name and return what is in the PO column value. Here is an example for your reference:

let
    Jobs = Table.FromRecords({[Customer="Customer1", Site Name="Site1"], [Customer="Customer2", Site Name="Site2"], [Customer="Customer3", Site Name="Site3"]}),
    POs = Table.FromRecords({[Customer="Customer1", Site="Site1", PO="PO1"], [Customer="Customer2", Site="Site2", PO="PO2"], [Customer="Customer3", Site="Site4", PO="PO3"]}),
    Joined = Table.NestedJoin(Jobs, {"Site Name"}, POs, {"Site"}, "Joined", JoinKind.LeftOuter),
    AddedColumn = Table.AddColumn(Joined, "PO", each if [Joined][PO] <> null then [Joined][PO] else Table.SelectRows(POs, each [Site] = [Site] and [Customer] = [Customer])[PO]{0})
in
    AddedColumn

In this example, Jobs and POs are the two tables you want to join. Table.NestedJoin is used to join the two tables based on the Site column. The JoinKind.LeftOuter parameter specifies that all rows from the Jobs table should be included in the result, even if there is no match in the POs table.

The if statement is used in the AddedColumn step to check if the PO column in the Joined table is not null. If it is not null, then the value in the PO column is returned. If it is null, then the Table.SelectRows function is used to filter the POs table to only include rows where the Site and Customer columns match the current row in the Jobs table. The {0} at the end of the expression is used to return the first value in the resulting column, which should be the value in the PO column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.