Excel and PowerQuery and PowerBI, oh my

Excel

Calendar heatmap in Excel using conditional formatting
Calendar heatmap in Excel using conditional formatting.

Excel is the least sexy data visualization tool. Some people go even further and question if what Excel makes is data visualization at all. Excel is a spreadsheet, a general-purpose tool to play with numbers, so it’s a little unfair to compare it to data visualization tools like Tableau or most programming languages.

Excel could indeed do a much better job at visualizing data. Its defaults (color palettes, chart library) are rudimentary at best, and most users don’t go beyond them. This is unfortunate because Excel is so flexible that you can use it to make almost any chart you want. You can mix doughnut charts with scatterplots to create gauges (not something that you should do regularly, though) or use error bars to make dot plots. You can use multiple geometries like bars, lines, or points. You can format a single data point instead of a whole series.

The problem with Excel is not that you can’t make a chart, but if you should do it. You can spend hours figuring out how to make a chart in Excel, while other tools make it with a simple drag and drop. Here are some examples that you will not find in the Excel charts library:

Heatmap Marimekko Gauge with multiple time series
Unusual Excel charts.

If you are a regular Excel user and haven’t outgrown Excel yet, there are two core skills that you need:

  • strong data visualization foundations to know what type of visualization you need to answer a specific question, and force Excel to answer it;

  • more familiarity with data structures.

A clear separation between a functional data table you use as a data source, and a formatted table you use to present it is not common in the Excel world. You can run out of rows, but running out of columns is a strong sign that you probably are using Excel the wrong way. This happened in late 2020 in the UK with Covid-19 data. People blamed Excel, not the lack of data skills (I’m commenting on how it was reported, facts may be more nuanced and complex).

In summary, Excel is a great tool to learn about data visualization, and you can create complex charts that other software will not allow you to. This comes at a cost, so you should always analyze if your requirements and resources would be better served using a different tool.

PowerQuery

Data cleaning and structuring using PowerQuery
Data cleaning and structuring using PowerQuery.

I don’t know how I survived so many years using Excel without PowerQuery. It’s a great tool to connect to many data sources, clean and prepare the data for analysis. Annoying tasks like un-pivoting columns, splitting text columns, merging, and appending tables can be performed with a few clicks and are much faster than creating a complex Excel formula that takes ages to calculate. As a positive side effect, using PowerQuery also makes you more aware of the need for better data structures in Excel, as discussed above.

PowerBI

Designing a dashboard in PowerBI
Designing a dashboard in PowerBI.

I have a lot less experience with PowerBI than with Excel, and I expect my perspective will be evolving over the near future. PowerBI is a much more structured environment than Excel and uses a reasonably complex data manipulation language (DAX).

I wrote about PowerBI as a visualization tool here and about its core visuals. For an advanced Excel user that knows a few things about data visualization, I think the whole experience is somewhat painful, even if we take its young age into account, and compares poorly to its more mature data handling capabilities.

On the bright side, after “key influencers,” PowerBI now allows you to display a marker on a line chart signaling each anomaly. Some statistical analysis baked into charts is a promising idea that hopefully will be improved and generalized.

If the core visuals don’t answer your needs, you can search the marketplace for a custom visual. The Small Multiples custom visual is excellent and free.

Small multiples in PowerBI using a custom visual
Small multiples in PowerBI using a custom visual.

Some custom visuals are extremely expensive, compared to a PowerBI license, so this must be factored in when you compare it to other BI products.

Without custom visuals, I don’t think PowerBI can be a serious competitor in the data visualization market. I wrote, in a somewhat provocative tweet, that PowerBI is 80% data and 20% visualization, while Tableau is 80% visualization and 20% data. Both products are great, but they have different natures, and you should manage your expectations accordingly.