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 I
Super User I

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors