Clearly and Simply

Create a Summary Table in Power Query

How to create a Summary Table in Power Query with aggregated values of the original data source without importing the entire data into Excel

A frequent use case of Power Query (aka Get and Transform) is to connect to an external, big data source, filter and remove data in a query and load only a fraction of all rows into the Excel workbook.

This will ensure you only carry along the parts of the data in your workbook you really need and will thereby keep the size lean and the performance fast.

Although you do not need the original data on row level in your workbook, you might be interested in a couple of aggregated measures of the original data table, e.g. the total sum of a column, the count of rows, the distinct count of entries (unique values) in a column, etc.

Today’s post explains how to create a Summary Table with defined aggregations on the original data without loading the entire source. The key of the solution is the M-function #table.

As always, the post comes with an example workbook for free download.

Filter Excel Dashboards with Slicers and Timelines

How to use Table Slicers and Timelines as interactive Filter Controls on a Microsoft Excel Dashboard

The recent posts

Filter Excel Dashboards with Table Slicers

and

Showcase for Table Slicers on Excel Dashboards

described a technique how to use Excel’s popular Slicers on tables as easy-to-use, interactive filter controls on a dashboard.

Although the approach can quickly be implemented and is working fine, it has one major shortcoming: for whatever reason, timelines are only available for Pivot Tables, not for tables. If you have a date dimension in your data (and according to my experiences many data sets do), you can’t let the user filter by dates with a timeline on a table.

Today’s article will describe a work-around to eliminate this shortcoming. As always, the post provides the example workbook for free download.

My 2 Cents on the COVID-19 Dashboard by JHU

For what it’s worth: a few remarks on the currently extremely popular COVID-19 Dashboard provided by the Center for Systems Science and Engineering (CSSE) at Johns Hopkins University (JHU)

When the Corona pandemic started, I was tempted to build and publish a dashboard about the spread of COVID-19, too. But then I decided not to. Not only because much brighter people than me beat me to it, but also because the topic is very sensitive. People suffer and people die. Not the type of data I want to analyze and visualize. Hence, I stayed out, stayed home and wrote a few articles about something else (see my recent posts).

Today I changed my mind. I still don’t want to create a comprehensive COVID-19 dashboard, but I would like to add my 2 cents to the definitely most popular Corona Data Visualization: the COVID-19 Dashboard provided by the Johns Hopkins University:

This afternoon, I read that this dashboard is currently clicked 1.2 billion (!) times a day. 

First things first: it is a great dashboard providing the most important numbers at a glance and various options to drill down into the details. I am very impressed by its features, how quickly this visualization was made available and how often the underlying data is updated. My congratulations to the data science team of the JHU.

Having said that, I also see a few weaknesses and today’s post will discuss two of them.

Heat Maps with Individual Color Scales in Excel

How to create Heat Maps in Microsoft Excel using individual Color Scales to support the analytical insights into the distribution of the data

In his brilliant guest post Analytical Color Scales for Heat Maps, Ron Whale explained, how color scales can efficiently support the process of analyzing data.

Applying different, thoroughly designed color schemes can help to gain deeper insights into the distribution of data, to identify outliers, to focus on special points of interest, to find similar groupings and more.

In a nutshell: switching between color scales of a Choropleth or Heat Map helps you to understand your data.

Whilst Ron was developing his great palette of color schemes for using them on a Choropleth Map, they are equally helpful for a much simpler visualization technique: a Heat Map of a table (or range) of numbers.

Microsoft Excel does provide a built-in feature to create a Heat Map on a range of numbers: Conditional Formatting. However, this is limited regarding the configuration of the color scales and it does not provide the option to easily switch between color schemes for supporting the data analysis process.

Today’s post provides a technique to overcome this shortcoming of Excel: a VBA-based solution to easily apply any given color scale to an Excel range with only two mouse clicks. As always, the post comes with the workbook for free download.

Analytical Color Scales for Heat Maps

More sophisticated Color Scales to increase the analytical insights provided by Heat Maps or Choropleth Maps – a Guest Post by Ron Whale

The workbook posted with the recent article Geographical Flow Maps in Excel (Part 3 of 3) provided new color scales, I never published before. Truth be told, I did not develop these schemes. Ron Whale did. Ron generously agreed to share them here and he is even kind enough to explain the scales, their use cases and the value they add to analytical Heat Maps in today’s guest post. Enjoy.

Just a few months ago, I found this website and discovered a great Choropleth mapping spreadsheet. I really like the color mapping schemes that have been developed. As I worked through the program and played with the mapping colors, I began to understand that the colors could be used to highlight additional data details in the map.

So, I came up with a number of special purpose color schemes designed to enhance specific aspects of the data and added them in with some of the original color scales. As the color schemes grouped into different visual purposes, I named the new colors to better describe the type of data that would be highlighted when using that scale.

I thought I would share my twist on the color scales and will explain my thoughts about the different scale types below.