Tracking COVID-19 Data in Excel using Power Query

Visualizing, and subsequently observing trends in data is very important for making any sense of it, and hopefully plan accordingly. Likewise, tracking the spread of COVID-19 has been paramount in understanding how, where, and when it has been spreading, and ultimately help us shed some light to the situation. If you want to build your own dashboard in Excel, you've come to the right place! In this guide, we're going to show you way of tracking COVID-19 Data in Excel using Power Query. These methods, of course, apply to pretty much any other similar data set.

Download Workbook

What is Power Query?

Power Query is a business intelligence (BI) for Excel. It is also known as Get & Transform in Excel 2016 and newer versions. Essentially, Power Query is a tool for connecting, fetching, and modifying data from external sources before populating it into a worksheet. Check our Power Query guide for a quick start: https://www.spreadsheetweb.com/power-query-basics/

Covid-19 Data

We are going to use data made available by covidtracking.com - The COVID Tracking Project. The site is a volunteer-driven effort that collects and publishes the data about the COVID-19 outbreak in US.

The COVID Tracking Project site provides data in both JSON and CSV formats. We are going to use CSV file for Historic values for all states to fetch the data. You can access detailed information about the data and download links at https://covidtracking.com/api.

Creating the query

First, we need to fetch the data. The following steps below,  you can fetch the data to track COVID-19 Data in Excel. Please note that some of names may be different than your Excel version, because there can be discrepancies between Excel versions and updates.

1. (Excel 2016 and newer) Data > Get & Transform Data (Section) > From Web
   (Excel 2013 w/ Power Query) Power Query > Get External Data (Section) > From Web
   
2. Enter the url of data you want to get and click OK. We preferred https://covidtracking.com/api/v1/states/daily.csv
   
3. You may see the authentication dialog. If you are using The COVID Tracking Project’s like in this example, you can continue as Anonymous by clicking Connect. Otherwise, use the dialog to authenticate for your data source.
   
4. The next dialog displays a small part of the source data. If you are satisfied with it, click Edit (or Transform).
   
5. The Edit button displays the Power Query Editor which is a powerful tool to shape and make it ready for Excel. Please see the Power Query Basics article for further information, if you haven’t already.
   
6. Now, it is time to change the layout of the data-set. First, start by removing any unnecessary columns. You can either select them individually and delete unwanted columns, or select the ones you need and delete the others. The example below does the latter.
   
7. You may have noticed that Power Query defined date column as a numeric If you are OK with this format, you can skip this step. However, we suggest changing it to date data type for consistency. You can convert these date numbers to actual dates using formulas as well. Here, we will show you an even faster way:
   1. Right click > Change Type > Text
   2. (Again) Right click > Change Type > Date
      
8. You can reorder the columns as you like.
   
9. Once you satisfied with the data, click Close & Load button in Home tab of ribbon to send your data to an Excel worksheet.

Tracking COVID-19 Data in Excel

After clicking Close & Load, Power Query creates an Excel Table in a new worksheet and populates the query result in it. From now on, refresh your data whenever you want to access the latest data.

How to refresh the data

You can refresh your query in several ways:

1. Using shortcuts:
   1. Alt + F5 while selecting any cell in the table
   2. Ctrl + Alt + F5 to refresh whole workbook
2. Right-click menu of your query in Power Query pane
   
3. Ribbon
   1. Data tab
      
   2. Design tab (dedicated to the selected table)
      
   3.  Query tab (dedicated to the selected table)
      

Summation of data

Creating a "summary" of your data can help users get a better understanding of the bird's-eye-view of the dashboard. You can combine aggregation and lookup functions to do this.

Here are some sample formulas we added.

Latest statistics:

Statistics from start: