How to use "if" & lookup function together in a condition in power query/ power bi

[BijuDev]

Hello Team

Need support on how to use IF and Vlookup functions  together in power query or power BI. Example I have two data set like below.
I need to first look for column A, if column is not blank, vlookup the value in  second Data Set 2, retun the Division , else (if A column is blank) lookup the value in colum B ( ie PC column) , return the Division from Data Set 2 ( colum B )

DataSet 1

CC	PC	Amt
CC5900		10
	PC6902	45
CC5901		60

Data Set 2 ( Master)

CC/PC	Division
CC5900	Sales
CC5901	Marketing
PC6901	Sales
PC6902	Marketing

Output

CC	PC	Amt	Division
CC5900		10	Sales
	PC6902	45	Marketing
CC5901		60	Sales

[BijuDev]

Thanks Luca, I created a calculated column on Data Set 1 using above method. However,  I need one more column in Data Set 1 which should retun corresponding Divison Name from Data set 2 ( look up array)

[lucadelicio]

You have to connect the two table and create the measure to do the calculation.

[StrategicSavvy · Accepted solution]

hi @BijuDev ,

you can also consider using power query to do this. Here are the steps:

1. go to your Data Set 1 and make sure you have nulls in both column CC / PC

you can use replace values to null them

2. add new column in your dataset 1 using double question marks (coalesce operator)

you will get this column :

3. merge your dataset 1 with dataset 2

4. expand your table and select division column 

PBIX 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron 

[lucadelicio]

Hi,
try to craete the same CC/PC Column also in the dataset 1.
Add calculated column:
CCPC =
IF(ISBLANK(CC), PC, CC)
Then you can connect the dataset1 with dataset2 on CC/PC Column and do the calculation with the measure that you want.
Mark as a solution my answer if i help you.
Thank you.