Forum Discussion

vijenkin's avatar
vijenkin
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

IF Table Column Contains Matching Value Place Another Value in New Column

Hello I am currently trying to solve a problem in PowerBi. I have two tables that have no relationship to each other. Table A contains a column with text that is user generated. Table B contains two ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula works

    =if(ISBLANK(FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1)),Data[Original Title],FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1)&" ("&LOOKUPVALUE(code_names[Symbolic Name],code_names[Unique Code],FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1))&")")

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

I have lost track of your requirement now.  Share a small representative dataset(s), describe the question and show the expected result.

vijenkin's avatar
vijenkin
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Hi Ashish I have figured out the problem and solution. By utilizing FIRSTNONBLAN, the function was searching for the 1st matching instance of the Unique Code. As there are rows within the Unique Code table such as "0" and "0000" it would stop before it even got to the latter. As such I changed FIRSTNONBLANK to LASTNONBLANK and that has solved my problem. 

Thank you so much for providing the initial solution.  

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    You are welcome.