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

tazeo

Board Regular
Joined
Feb 15, 2007
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
Solution
Brilliant
Took me a bit to get it to work. But entirely my fault as I missed the "Split by Rows" part :eek:
Will try the anti add for the bonus points too 👊
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,579
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top