Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Text | Price |
Telephone jack connection at high speed | 50$ |
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:
Technology | Technology Lookup |
DSL | dsl |
DSL | telephone jack |
DSL | Telephone jack |
Coax | coax |
Coax | Cable tv |
Coax | Cable 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:
Text | Price | Technology |
Telephone jack connection at high speed | 50$ | 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.
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"
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! |
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.