Forum Discussion

NickDSL's avatar
NickDSL
Icon for Helper I rankHelper I
4 years ago
Solved

Text 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...
Need Help
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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

 

NickDSL's avatar
NickDSL
Icon for Helper I rankHelper I
4 years ago

I'm sorry I dont think I really understand how to implement this solution. I'm not the most advanced with power BI and could use some guidance in the right direction.

In the above screenshot I have pasted in the first block of code and it seems to be working kinda. But this is the first time I've used the advanced editor so I'm not too sure on how it works.

 

How would I change the longform data in the "Data" column. My data is data that would change month to month. 

 

Where would I paste the second block of code? If I just paste it right below I'm left with a "Token Eof expected." error. How would I also update this list?

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    You need to get data from your sources into PQ..Delete everything after Source statement.
    Let's assume you got it from Excel. Then Source statement generated will be like this

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

    Put a comma after source statement.

    Copy the entire code provided by me starting DictList. 

    Paste the code.

    So your code will become like this

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    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"

    You will need to change column names here. If your column name is not Word List, say it is MyColumn. Then [Word List] will need to be replaced with [MyColumn].