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

There has to be some spelling error or some extra spaces/invisible characters.

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

So I believe I figured what the porblem is.

 

In the code_names[Unique Code] table there is a row that contains "0 (Success)" because that is the error code for success. Originally I did remove that row as the report should only have failures so success is not needed. However, afterwards the column showed "1 (another Error name)" . Therefore this makes me believe that there is something with the function that is searching through the Unique Code table which does not account for values equal to the two examples I just listed. For more context the Unique Code table looks like the below and has over 60K rows. 

 

Unique CodeSymbolic Name
0Success
1Security Error
2Server Error
..........
000000Client Error
000001Network Error
.........
012345Network Error
67890Server Error
24680Client Error

 

I did try the query with just the examples I included in the original post and that produces the expected results, so I know the query works. However, given the code_names[Unique Code] table has more a lot more values than that is there some modification that should be made to the query?

 

  • 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.