Forum Discussion
Lookupvalue if value is blank
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.
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.
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
2 Replies
- amitchandak
Super User
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
Community Champion
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.