Efficient Keyword Searching with PowerBI: Tips and Tricks for OR Results

[tazeo]

I often find myself sifting through large amounts of data to identify relevant keywords, and as many of you may know, this can be a time-consuming and sometimes frustrating process, particularly when it comes to spelling errors.

To address this issue, I came up with the idea of using PowerBI to streamline the process. My plan was to set up a report or dashboard that would display the imported or updated data, and then allow me to enter the keywords I am searching for. The idea is that it would return any rows that contain any of the specified keywords.

However, I've encountered a problem. The searches or slicers I create in PowerBI are only providing AND results, which means it only displays rows that contain all the keywords entered. This is often problematic as it frequently returns nil results.

While I have come across some information on Google that suggests this is possible, I haven't been able to find any instructions that work. Although I found a solution using PowerQuery, I haven't tried it because I want to share this approach with my colleagues, and we frequently need to search for different keywords.

Therefore, I'm looking for suggestions on how to achieve the desired OR results. If anyone has any ideas or alternative solutions (besides PowerBI), I would be grateful for your insights

 

[Matt Allington]

I am working on a project that does this atm. In short

• create a table containing your data
• add an index column in PQ
• create a reference and create a second table from the first
• keep just your index column and the column with the words you are searching
• split the word column on space delimiter and split to rows
• load both to power BI, join on the index and turn on bI directional cross filtering

Get the SmartFilter from the custom visual store (OKViz) and add the word column on the single word column in table 2
Add table one to a table visual on the page with all the columns you care about. Filter away. The filter is a logical OR

The reason it works is the PBI DB is designed to work this way. It is terrible at doing a text search on a text column, but great at filtering rows in table 2 based on specific values.

for bonus points, create a stop word table in excel containing all the words you don’t care about (think a, the, then, an, etc). Simply type them in to a single column, load them up and do an anti join on table 2 to remove those before load. You can export the full list of words from table 2, open it in excel and use that to fast track your stop word table master data.

HTH

 

[tazeo]

Brilliant
Took me a bit to get it to work. But entirely my fault as I missed the "Split by Rows" part
Will try the anti add for the bonus points too

 

[tazeo]

For those playing along at home, I added a page to the report option of PowerBI. Made a quick Matrix Table of the info of the second table, and did a count of the words to identify the frequent filler words.