Blog Post

Power BI Community Blog
3 MIN READ

Dynamic text search in Power Query

Migasuke's avatar
Migasuke
Memorable Member
3 years ago
Use case description Imagine we own several stores in one big shopping mall. We noticed that people write their reviews about all stores on the website of the shopping mall. What does it mean? It ...
Updated 3 years ago
Version 6.0
how to
tips & tricks
Migasuke's avatar
Memorable Member
Joined June 17, 2021
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Thank you for replying.  This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Comments"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Accumulate(#"Stores (3)"[Stores],"",(State,Current)=>if Text.Contains([Comments],Current,Comparer.OrdinalIgnoreCase) then Current & ", "&State else State)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Combine(List.Select(Text.Split([Custom],","), each _<>" "),", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

Hope this helps.