LOOKUPVALUE WITH IF

Hi Paolo,

I can understand your question just fine. You will need to perform two merges that will perform just like you described. Lookup Table1 into Table2 by customer number to cust and call this Lookup1. Now lookup Table1 into Table2 by iso to country. Expand Lookup1 into Market Responsiblity and Lookup2 into Customer Market New Area. Next put in a calculation field that will pull the Market Responsiblity if it is not null and will otherwise return the Customer Market New Area. Finally, remove the Market Responsiblity and Customer Market New Area.

Regards,

Mike

let
    Source = Table1,
    #"Merged Queries" = Table.NestedJoin(Source, {"customer number"}, Table2, {"cust"}, "Lookup1", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"iso"}, Table2, {"COUNTRY"}, "Lookup2", JoinKind.LeftOuter),
    #"Expanded Lookup1" = Table.ExpandTableColumn(#"Merged Queries1", "Lookup1", {"Market Responsibility"}, {"Market Responsibility"}),
    #"Expanded Lookup2" = Table.ExpandTableColumn(#"Expanded Lookup1", "Lookup2", {"Customer Market Area New"}, {"Customer Market Area New"}),
    AddLookup = Table.AddColumn(#"Expanded Lookup2", "Lookup", each if [Market Responsibility] <> null then [Market Responsibility] else [Customer Market Area New]),
    #"Removed Columns" = Table.RemoveColumns(AddLookup,{"Market Responsibility", "Customer Market Area New"})
in
    #"Removed Columns"