From a creative standpoint, it’s disheartening if you’re often trying to get users past a Table and Export mentality with Power BI. What otherwise could be a strong BI tool ends up simply being used as middleware for Excel. Alongside conversations ad nauseum around the export limit of 150,000 records, you end up not so much visualizing data as building what ends up looking like a spreadsheet. Since it isn’t a spreadsheet though, it still needs to be exported. It’s a battle that may eventually be won with continual education around Power BI’s full capabilities, but it’s easy to get worn down in the process if it’s a common situation for you.
You may have already provided possible alternatives to try to gradually empower users to use Power BI’s broader features. If not, here are some common techniques and suggestions:
1. Conditional formatting
Conditional formatting for a table or matrix is an easy way to keep the grid and display raw numbers while providing visual cues that our brains process faster than reading numbers. Background color, font color, icons, and data bars help jazz up bland tables. It’s an easy compromise that brings some data visualization to table detail.
2. Any other type of visual alongside a table
Perhaps viewers simply don’t know that other types of visuals exist. If not, make sure they do! Educate that with a bit of imagination, you can do more. Sure, keep the detail in the table, but don’t start with it. Try crafting a hybrid report page that displays interactive bar charts alongside a table, for example. Demonstrate cross filtering between the summary level visuals you build and the table.
3. Drillthrough / Drill Through / Drill-Through pages
If your viewers are ready for it, a table could still exist but shift to another page. Your other summary level visualizations could be the primary focus, and you can demonstrate how to filter to details on the secondary drill through.
4. Report Page Tooltips
Building on the same concept as drill-through, your summary visuals are front-and-center, but you can visualize one or more charts or tables in your tooltips. The tooltip filters based on your selection, and you lose the ability to directly export a table. At this stage though, given enough exposure to table alternatives, your viewers may end up realizing that Power BI can do some of what they might already be doing in Excel, and that an export may not actually be needed.
5. Paginated Reports
If you really can’t get anywhere with interactive, summary level analysis first; build a table into a paginated report and show the robust export capabilities. Note that while paginated reports could do exactly the export to Excel the users might want without the trouble of visualizing data, it’s important that they see the alternatives first. While powerful for their own use cases, paginated in this sense is a “settle” option after the aforementioned alternatives are exhausted.
With Premium Per User licensing, Power BI’s paginated reports are now even more available than they traditionally have been. There are also tons of Reporting Services developers with tablix skills from years building these reports in SSRS and now Power BI. Plus, there’s a talking stuffed mascot named Paginated Report Bear.
6. Next-level table styling
If education and alternatives do not seem to work, the following technique brings a level of nostalgia to Power BI. Perhaps this could be your April Fool’s Day joke.
- Add your data into a Power BI table. The default style already alternates white and grey rows.
- Change the header and value font to Courier New
- Change the Alternate Background Color to a light green like #E4F4ED
- Create a measure that contains a simple centered dot using UNICHAR(8226)
- Add your dot measure as the first and last value in your table. Voilà!
- Export to PDF and print!