Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrLowland
Frequent Visitor

matching two text columns in two different tables by a part of the words

Hello,

 

I want to compare two columns in two different tables by only a part of the text. After that I want to count them.

 

Let me try to explain what I mean.

 

Table-1: contains the name 7-zip.

Table-2: contains the same name only it also has the version in its name. E.g 7-Zip 15.14 (x64), 7-Zip 16.02 (x64 edition), 7-Zip 16.04, ....

 

If you use a slicer on table-2 you get al the 7-Zip xx.xx names. How do I compare these two tables so I can count how many 7-Zip files there are in table-2.

 

1 ACCEPTED SOLUTION
gooranga1
Power Participant
Power Participant

Could you use LOOKUPVALUE? It depends on how clean the data is, so this has to have a space " " between the name and version number. 

 

LU = LOOKUPVALUE(Table1[Column1],Table1[Column1],LEFT(Table2[Column1],FIND(" ",Table2[Column1])-1))

 

lookup_value.PNG

View solution in original post

9 REPLIES 9
gooranga1
Power Participant
Power Participant

Could you use LOOKUPVALUE? It depends on how clean the data is, so this has to have a space " " between the name and version number. 

 

LU = LOOKUPVALUE(Table1[Column1],Table1[Column1],LEFT(Table2[Column1],FIND(" ",Table2[Column1])-1))

 

lookup_value.PNG

Hi Gooranga1,

 

Could you use LOOKUPVALUE? It depends on how clean the data is, so this has to have a space " " between the name and version number. 

 

The formula works great. Can you explain how it works?

Because I add more software where the name contains more spaces. E.g. Microsoft Office 365 ProPlus. Now it will not give me the right software name in LU.

 

Table1 contains:

Table1.gif

 

Table2 contains:

Table2.gif

I do not see Microsoft Office at Microsoft Office 365 ProPlus. Can you explain why?

 

Hi @MrLowland,

 

Yes it's not going to work well for software names with spaces. The issue you will have is that you a need clear delineation between the software titles/names and the subsequent version numbers.

 

The LookupValue takes a value from 1 table and then matches that to another table, in the example posted I used the space between the name and the version numbers to strip out the software name. BUT it relies on the space between version and software name. 

 

If your software name is going to contain spaces as well then working out when the versions start and the software names ends becomes quite challenging and without that matching between 2 tables is also very tricky.

 

Do you have any control over the format of this data?

 

 

Hi @gooranga1,

 

Thanks for your explanation.

No I do not have any control over the format of this data. It is already in a system end I only can export it.

Hi,

 

Not sure you need a lookup but a way of stripping out the software name into a new column? If the data is that inconsistent you may have to clean it manually. How many rows is it in total?

Hi,

 

It's about 2400 rows.

Greg_Deckler
Super User
Super User

Perhaps use the multiple selections of the slicer coupled with the Search functionality of the slicer.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I want to compare two columns in two different tables by text. After that I want to count them . For Example Equipment Table Having Status Column with value “Not Submitted” & Same Values with Same Column Name in MASTER LIST Table. So I want total “Not Submiited” from Both table. Attached alos is PBX filehttps://www.dropbox.com/s/aqpwx0ov1t5j6z1/Closeout%20Summary%20-%20Mobility%20Pavilion.pbix?dl=0

Hi Smoupre,

 

Perhaps use the multiple selections of the slicer coupled with the Search functionality of the slicer.

 

Thanks for answering. It is a solution but it is to muchwork. I would like to have it automated so you can see the total at a glance. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.