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
Anonymous
Not applicable

Add text column based on words matching in two different tables

Hello, 

I have searched this forum and the web to figure out my problem, unfortunately with no luck. I really hope someone can help me with a solution for the following problem: 

I have extracted a table (Table A) from Html containing a text column and a price column. Shortened example presented below: 

 

TextPrice
Telephone jack connection at high speed50$
Fast speed through dsl connection

35$

Super fast speed through cable TV

40$

 

The text column contains a word which indicate the technology of the service. I could add a conditional column for each table spelling out the right/wrong ways, but since i need to do it for multiple providers i have to create a formula, which can be used for all. 

Therefore i have created a table (Table B) containing two columns: 1) The technology spelled the correct way and 2) all the variations of the ways to spell the technology. See example below: 

TechnologyTechnology Lookup
DSLdsl
DSLtelephone jack
DSLTelephone jack
Coaxcoax
CoaxCable tv
CoaxCable TV


So what i am trying to do is to create a column, which will check to see if one of the words in the "Text" column (from table A) matches a way of spelling the technology in the "Technology lookup" column (in table B) and finally returning the value from the "Technology" column (in table B) to the new column in Table A. 

The modified table A should look like this:

 

TextPriceTechnology
Telephone jack connection at high speed50$DSL
Fast speed through dsl connection

35$

DSL

Super fast speed through cable TV

40$

Coax

 

Thanks in advance! Any help would be much appreciated. 

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

let
    Lookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcgn2UdJRSinOUYrVgfFKUnNSCzLy81IVshKTs8ESzvmJFUCZZBCFzE9MyklVKClTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Technology = _t, #"Technology Lookup" = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYsxDoAgEAS/cqH2HX5AYkMoTlgFJRwB/L9GGxPLycwYozQSSpAM2tkd5CRnuB4lE3cKcQvUCuCVHYwaufUXqYcq5y19S5/nqaazoNL6bx0vCaRnZe0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Added Custom" = let lookup = Table.ToRecords(Lookup) in Table.AddColumn(Source, "Technology", each List.Accumulate(lookup, [Text], (s,c) => if Text.Contains(s, c[Technology Lookup], Comparer.OrdinalIgnoreCase) then c[Technology] else s))
in
    #"Added Custom"

Screenshot 2021-07-06 172003.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Thank you so much! I am quite new to Power BI, hence this might be a simple/stupid question: 

If my lookup table is called DimTech where would i put that in the formula? At the moment, the formula adds a column inclucing a table as seen below instead of just adding a Technology column as your presented picture shows. 

Dataman44_0-1625650236577.png

 

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.

Top Solution Authors
Top Kudoed Authors