Forum

searching for text ...
 
Notifications
Clear all

searching for text string - faster way

6 Posts
4 Users
0 Reactions
171 Views
(@hvhue)
Posts: 3
Active Member
Topic starter
 

Hi ,

Continuing searching text for string in power query with a table over 14,000 rows.

I'm attaching the data here to do some testing. 

H - Thanks 

 
Posted : 22/12/2020 9:43 am
(@mynda)
Posts: 4762
Member Admin
 

Hi and welcome to our forum!

Your file didn't get attached. Please try again and be sure to click the 'Start Upload' button after selecting your file.

Mynda

 
Posted : 22/12/2020 11:27 pm
(@hvhue)
Posts: 3
Active Member
Topic starter
 

Hi, uploaded the file again. 

 
Posted : 23/12/2020 9:55 am
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi,

Have you read and tried the code from these posts

https://www.myonlinetraininghub.com/create-a-list-of-matching-words-when-searching-text-in-power-query

https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query

You haven't said how you want the results to look so I've produced a table with the strings that match in a separate column - see attached.

Regards

Phil

 
Posted : 23/12/2020 7:06 pm
(@catalinb)
Posts: 1937
Member Admin
 

You can also try this query, might be faster:

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
WordList = List.Buffer(Excel.CurrentWorkbook(){[Name="WordList"]}[Content][Parent Item]),
#"Added Custom1" = Table.AddColumn(Source, "Custom", (x)=> Text.Combine(List.Select(WordList, each Text.Contains(x[item], _ ,Comparer.OrdinalIgnoreCase) ),","))
in
#"Added Custom1"

You can remove the red argument if Case should not be ignored.

 
Posted : 24/12/2020 12:36 am
(@hvhue)
Posts: 3
Active Member
Topic starter
 

Thanks for helping Philip & Catalin.

I just started to learn PQ for 2 weeks ago. 🙂 

Learning form your posts that makes me spent a lots of time but really helpful everyone.

Can you share more stuff to learn PQ? Highly appreciated. 

Thank you!

Hue

 
Posted : 24/12/2020 5:35 am
Share: