Forum Discussion
NickDSL
Helper I
Helper IText Analytics extraction based on a defined list of words
Hello. I was wondering if it would be possible to give Power BI a long list of freeform text and also give it a "dictionary" of predefined values then have it search our long list of freeform tex...
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVJBbsJADPyKlXNVIVBVcYRWapHghnqhHJysQyw2a+R1iOhr+pa+rJtAWqkq7c2enZn1jnezyWY1Htlhtr3ZZBgcaZQANdWiJyhQCXIqsIkEbBEKL5EUTGDH6ktWCi6JoMSa/an3WFM0Druzn+ai4BOjGaBZD61JFQuCxfIv0CeRSfh4P/cx1fCE6ijEa8gR/ZdgTrqPsCSOTXrGko/DDHPfEDyTSvjRDsJ/gVx5V1mLRtr3T0IRoil2cBfPywq6XKhG9uDSaXhtRqPxvUErur+4PEgwLAw4lHKV9Jt1W1GARVqP9xfWinRHDlq2CgbbxSNM7ibT6fT2fFuVdgdzFdk79N97lRKsIoj8NtSsMFvCODEcTCDH5IkHVKspWG8VDU8pzDQ4LIJjDOfv46VA47QSjhDEUgIghw7ItttP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), DictList = List.Buffer(Dict[Word List]), #"Added Custom" = Table.AddColumn(Source, "Result", each List.First(List.RemoveNulls(List.Transform(DictList,(x)=>if Text.Contains([Data],x, Comparer.OrdinalIgnoreCase) then x else null)))) in #"Added Custom"Code for table named Dict
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLbbsIwDP0Vi+f9BN1VE9UQTLwgHlxqSlQ3QU5a1r+fO5LSor3Z5zi2z3H2+8WyKKiHF2LYYA9b14bz4vA04CSWwrmHFbZC7CFHU5K9kSXaQDEsja2Cs/MMPtuLCSQP6JrxSP9h8MyEorPqO+0nTf1Q6IwNEXGCMRKDwRxjdjoZa0I/SyB3uqyIytwZZqziBMbGqWh7jIJZFYdOKxJftM2MV/UPuTRo4Qtrf0d8hckZph/jYakN7RR49UFL0hOGLaGqG3OqxE1jyFxvB58ywnH0jZl5HLE1Ss1oyxm4IYvGe909rcaDo39iIScs3TXNv5xRKIToJwfcmRLhzUmAvFd5EW+T/3bQnA4lhRtuaIfTTpBvdX+8u/D0v/hB2TuKehtbd8iRzEhqDysyXj8grEyXumbcEnyQpDox1TlccbSiwU6XXhwOvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Word List" = _t]) in Source
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalPlease post your data for us to have a look.
NickDSLI cannot post my exact data but here is some dummy data:
Data:
Longform Data Expected Result From Query Amavida Amavida anderson memory care because its closer to girlfirendand family Anderson Testing arbor landuing Arbor Landing Arbor Terrace IL Arbor Terrace Arbor Terrace IL Arbor Terrace Arlington Arlington Aston Gardens Aston Gardens Aston Gardens Aston Gardens Avalon Avalon Berks Leisure Living Berks Leisure Living Blue Heron Blue Heron Blue Heron Blue Heron Blue Heron Blue Heron Blue Heron Blue Heron brightwater Brightwater Goes straight to VM and email doesn't work Contact info doesn't work Goes straight to VM when I call Merged with Contact ID 353999. Chose Brookdale because of the size of their AL 2 bed 3 bath apartment Brookdale staying in Indiana location is not an option "Dictionary"
This would be a long list of competitor/community names. Here is a short list for an example. It would be MUCH longer (1000+ items)
Abbey Del Ray South Abernethy Laurels Maiden Adante Addington Addington Jupiter Addington Place Addington Place Clear Lake Addison Addison Point Adora Adriatica Affinity Affinity Monterrey Village Alamo Ranch Albertville Album Ranch Alden Ranch Alderman Oaks Aldersgate Alexis Allen Alexis Estates All Seasons Allegro Allegro Boynton Beach Allegro Jupiter Allegro Parkland Allegro Renaissance Allisonville Meadows Alpharetta AltaVida Fort Myers Alura - Vijay_A_Verma
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVJBbsJADPyKlXNVIVBVcYRWapHghnqhHJysQyw2a+R1iOhr+pa+rJtAWqkq7c2enZn1jnezyWY1Htlhtr3ZZBgcaZQANdWiJyhQCXIqsIkEbBEKL5EUTGDH6ktWCi6JoMSa/an3WFM0Druzn+ai4BOjGaBZD61JFQuCxfIv0CeRSfh4P/cx1fCE6ijEa8gR/ZdgTrqPsCSOTXrGko/DDHPfEDyTSvjRDsJ/gVx5V1mLRtr3T0IRoil2cBfPywq6XKhG9uDSaXhtRqPxvUErur+4PEgwLAw4lHKV9Jt1W1GARVqP9xfWinRHDlq2CgbbxSNM7ibT6fT2fFuVdgdzFdk79N97lRKsIoj8NtSsMFvCODEcTCDH5IkHVKspWG8VDU8pzDQ4LIJjDOfv46VA47QSjhDEUgIghw7ItttP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), DictList = List.Buffer(Dict[Word List]), #"Added Custom" = Table.AddColumn(Source, "Result", each List.First(List.RemoveNulls(List.Transform(DictList,(x)=>if Text.Contains([Data],x, Comparer.OrdinalIgnoreCase) then x else null)))) in #"Added Custom"Code for table named Dict
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVLbbsIwDP0Vi+f9BN1VE9UQTLwgHlxqSlQ3QU5a1r+fO5LSor3Z5zi2z3H2+8WyKKiHF2LYYA9b14bz4vA04CSWwrmHFbZC7CFHU5K9kSXaQDEsja2Cs/MMPtuLCSQP6JrxSP9h8MyEorPqO+0nTf1Q6IwNEXGCMRKDwRxjdjoZa0I/SyB3uqyIytwZZqziBMbGqWh7jIJZFYdOKxJftM2MV/UPuTRo4Qtrf0d8hckZph/jYakN7RR49UFL0hOGLaGqG3OqxE1jyFxvB58ywnH0jZl5HLE1Ss1oyxm4IYvGe909rcaDo39iIScs3TXNv5xRKIToJwfcmRLhzUmAvFd5EW+T/3bQnA4lhRtuaIfTTpBvdX+8u/D0v/hB2TuKehtbd8iRzEhqDysyXj8grEyXumbcEnyQpDox1TlccbSiwU6XXhwOvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Word List" = _t]) in Source- AlexisOlson
Super User
NickDSL This solution looks good.
If you want to match multiple dictionary words, then you can update the #"Added Custom" step to concatenate them:
Table.AddColumn(Source, "Result", each Text.Combine(List.Select(DictList, (x) => Text.Contains([Data], x, Comparer.OrdinalIgnoreCase))))
