Lookupvalue if value is blank

[Anonymous]

Hi, I am currently trying to do something very simple.

I want to create a new column, such that it is the same as another column BUT where the 'other' column is blank it performs a lookupvalue function to fill it.

Example below:

Table ONE                                   

A	B
key1	hi
key2	bye
key3
key4	ok

Table TWO

Key	Value
key1	hi
key2	bye
key3	so
key4	ok

I want Table ONE to become:

A	B	C
key1	hi	hi
key2	bye	bye
key3		so
key4	ok	ok

I have written this code:

C = IF( isblank( `Table ONE`[B]), lookupvalue( `Table TWO`[Value],`Table TWO`[Key], `Table ONE`[A]), `Table ONE`[B])

But for some mysterious reason it doesn't work and Column C becomes exactly the same as Column B.

Anyone have a ny ideas would be appreciated.

[Greg_Deckler · Accepted solution]

Could be a couple of things. Looks like you are using the alternate result parameter for LOOKUPVALUE that makes the value your B column in the even of one of two things. Either LOOKUPVALUE is not finding a single row or LOOKUPVALUE is finding no rows. 

You can find out by using COUNTROWS with a FILTER that mimics your LOOKUPVALUE search. That will tell you if you are getting no rows or multiple rows. You could use a MAXX(FILTER(TWO,TWO[Key]=ONE[A]),TWO[Value]) if you are getting multiple rows. Or MINX. If you are getting no rows then perhaps one of your key's has a space at the end or something like that.

[amitchandak · Accepted solution]

You can try, a New Column in Table

new column Table 1= MAxx(filter(Table2,table1[A] =table2[key]),table2[Value])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Greg_Deckler · Accepted solution]

Could be a couple of things. Looks like you are using the alternate result parameter for LOOKUPVALUE that makes the value your B column in the even of one of two things. Either LOOKUPVALUE is not finding a single row or LOOKUPVALUE is finding no rows. 

You can find out by using COUNTROWS with a FILTER that mimics your LOOKUPVALUE search. That will tell you if you are getting no rows or multiple rows. You could use a MAXX(FILTER(TWO,TWO[Key]=ONE[A]),TWO[Value]) if you are getting multiple rows. Or MINX. If you are getting no rows then perhaps one of your key's has a space at the end or something like that.