Revolutionize Power BI with the Search Box Visual

Key Features:

• User-Friendly Interface: Enter terms, press return, and watch your report filter in real-time.
• Advanced Search Options: Perform phrase searches with quotes and toggle the Boolean operator to ‘OR’ for broader term matching.
• Localization: Defaults to the report theme’s colours and fonts, and the language settings of the user’s browser
• Customizable Design: Tailor the search box, placeholder text, and search button to fit your report’s style.
• Optimized Performance: Enjoy fast and efficient searches, even with large data sets.

Until today there were three visualizations with text searching capability – two are restricted to exact phrase matching, and the other offers far more than a basic search – and is priced accordingly.

The Search Box – A Custom Power BI Visual

Enter the Search Box – a simple visualization that works in a familiar way – people can enter any terms, press return, and the page will filter to items matching all of them; entering terms in quote marks will run a “phrase search”; and an optional AND/OR toggle allows users to change the behaviour to filter the page to any of the terms entered is also available:

A ‘clear terms’ icon appears as soon as any characters are entered into the search box; instructions appear on hover; and pressing enter performs the same action as pressing the ‘Search’ button which changes to a clock while the search is being performed. Here’s an example showing the search button with a red background, and searching for items which contain any of the entered terms:

Hovering over elements of the The Search Box generates context-sensitive help.

Formatting Options

Though the Search Box ought to default to the current theme’s colours and fonts, many options are available to edit the visual’s look and features:

Optimizing the Search Index Column

The visualization is optimized for performance, taking the same time to load and run as the Microsoft Text Search visualization, all else being equal. Performance can be further improved, and this is especially recommended for large data sets, by optimizing the visual’s source column in Power Query following the steps below:

1. Duplicate or reference the original query
2. Remove columns other than the text and ID columns
3. Clean the text column (Transform/Format/Clean)
4. Lower-case the text column (Transform/Format/Lowercase)
5. Swap out diacritics (á, é, and so on) for their ASCII equivalents (a, e) using each Text.FromBinary(Text.ToBinary(_, 28597), TextEncoding.Ascii)})
6. Keep words only using each Text.Select(_, {"a".."z", " "})}
7. Remove HTML using each Html.Table(_, {{"TextIndex",":root"}})
8. Remove stop words (a, and, also, and so on) by prefixing and suffixing the text column with ” “, using the function in replace several characters in a column in just one power query step, and a query based on one of these stop word lists prefixed and suffixed with ” “
9. Trim the text column (Transform/Format/Trim)
10. Filter out values with less than three characters from the text column by:
    • Duplicating it the column
    • Extract number of characters
    • Filter to items greater than two
    • Remove column
11. Finally, remove duplicates

Save and close, then create a ‘both’ relationship between your new query and the original one on the ID column. This may impact phrase searching where stop words have been removed and a user searches for a phrase containing one.