cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twins2019
Frequent Visitor

LOOKUPVALUE WITH IF

HI

i have the following table2

custCOUNTRYMarket ResponsibilityCustomer Market Area New
 AEAsia MinorEMEA
 ALSouth East EUEMEA
 AMRussiaEMEA
 ANUSANorth America
 AROther South AmericaSouth America
 ATCentral EuropeEMEA
at99999ATItalyEMEA
at56432ATAsia MinorEMEA
at72444ATAsia MinorEMEA

 

i need a formula in table1 that find the column "market responsability" in table2 with these criteria :

if the customer number in table 1 is present in column "cust" in table2 then look for the market responsability in table2 

if the customer is not present, then it crosses me the column iso in table1 with column country in table2 and return the "market rasponsability" ... 

 

sorry for my bad english 😞

thanks to all

paola 

3 REPLIES 3
mcybulski
Solution Specialist
Solution Specialist

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"

 

 

I have to have in the table 1 the market responsabilty that is in table 2 crossing where is present the customer code of the table 2 and where the customer code is not present, the country code

I m working with power pivot and i have tried with IF and looukvalue but it return always ERROR
Nathaniel_C
Super User
Super User

Hi @twins2019 ,

Not quite sure what exactly the issue is here.  You may want to type it up in your language and use Google to translate.  I find it works quite well.

Thank you.

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors