cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vijenkin
Microsoft
Microsoft

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 columns that have a Unique Code and its corresponding Symbolic Name.

 

I want to "scan" each row of the column in Table A and IF a row contains the Unique Code from Table B, place the Unique Code and Symbolic Name from Table B into a brand new column in Table A. 

 

In short, I am trying to create a column that has the unique code and symbolic name information presented instead of the user generated information.

Example 

Data

 

Table A

Original Title
There is a problem with 012345
67890 spike over the last two weeks 
Many devices hitting 24680 as of last week

 

Table B

Unique CodeSymbolic Name
012345Network Error
67890Server Error
24680Client Error

 

Result

Table A

Original TitleNew Title
There is a problem with 012345012345 (Network Error)
67890 spike over the last two weeks 67890 (Server Error)
Many devices hitting 24680 as of last week24680 (Client Error)

 

Bonus Task

 

If the above can be solved then I will mark this problem as solved. However, it would be great if when the IF condition fails because no match is found the text in the Original Title column is returned

 

Original TitleNew Title
There is a problem with 012345012345 (Network Error)
67890 spike over the last two weeks 67890 (Server Error)
Many devices hitting 24680 as of last week24680 (Client Error)
Help needed for system problemHelp needed for system problem


I will not be able to share the PBIX file or provide screenshots of the actual data for privacy reasons

 

Any help or direction would be greatly appreciated. If this is not possible or more information is needed, please feel free to let me know. Thanks.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User III
Super User III

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi Ashish, I really appreciate the solution you provided. Unfortunately, when modifying the DAX to include the Data[Original Title] PowerBi does not recognize the column. For more context the table with the Original Title is being pulled in from Azure Dev Ops. Is there perhaps some kind of relationship or data type that needs to be had before the column can be recognized in DAX? 

 

Thanks

No relationship needs to be created.  I cannot say why it is not working on your file.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

So I figured out what the problem was. I created the column in the wrong table. I forget for certain DAX functions the corresponding value has to be in the same table. Now with that said I don't get any errors from the DAX but when activating the column all of the rows are 

 

0 (Success)

 

I am assuming this means the function is working but can't find anything. I checked the code_names[Unique Code] tablle and the codes that are in Data[Original Title] are indeed there.

 

Any thoughts? 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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?

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.  

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors