Forum Discussion

NSSLynne's avatar
NSSLynne
Regular Visitor
3 years ago

Power query lookup within and if statement

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 ...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.