A Step by Step Guide to Build Power BI Covid Data Reports from GitHub

Covid-19 is top of mind and getting to the data can be easy if you know how.  Currently, there is a GitHub project that is collecting data around Covid-19.  In this step by step guide, I show you how to use Microsoft Power BI and the Web Connector to access the time-series data.  The same process can be used for any GitHub repository if you have the proper URL.  Follow the steps below or watch my video to see exactly what you need to do for access to this dataset.  By the end of the guide you can build a Power BI Covid report from the GitHub data.

Many are familiar with the Johns Hopkins Coronavirus online tool.  Learning these skills you can make your own and use it on future projects that don’t involve this pandemic.

Learn More – Power BI Training

Learn More – Power BI Consulting

Learn More  – Microsoft Power BI

Get data from Power BI Web Connector

Once you have opened Microsoft Power BI.

The first step is to select the Get Data tab and navigate to the Web option.

A prompt will appear requesting a URL, in this example we got our URL from Github.

GitHub URL Location and Format

Below is the URL that grants access to the data Johns Hopkins University has gathered on Covid-19.  Copy and paste it into your Web Data Connector

The file has been updated on 3/22 to a new format that as of 3/26/2020 does not have State or Province populated.

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

Old File for reference only…

https://raw.githubusercontent.com/CSSEGISandData/Covid-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv

https://raw.githubusercontent.com/CSSEGISandData/Covid-19/master/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv

Transforming Data in Power Query

After inserting the link be sure to choose the transform data option so the table can be edited and transformed.

You are now transforming data in the Power Query editor.

The first step to format the table is to set the first row as headers.

This action is performed by clicking the “Use First Row as Headers” on the Home tab under the transform section.

Power Query Unpivot Example

Next, select the first four columns, then navigate to the transform tab.

In the “Any Column” section you will find unpivot columns,

click the arrow and select “unpivot other columns” from the drop down list.

Renaming Columns in Power Query

To make the data easier to understand we will be renaming “Attribute” column to “Date” and “Value” column  to “Cases” by right clicking the header of their respective rows.

The final step is to set the data types.

This option is found in the Transform tab in the “Any Column” Section, labeled “Data Type” and is set to text as default.

We will change the data type to date for the “Date” column and Whole Number for the “Cases” column.

Power Query Close and Apply

When you have completed all the steps click “close and apply”, the first option in the file tab.

This closes Power Query editor and returns you to Power BI Desktop.  You now have your Power BI Covid data imported for use.

Power BI Covid Simple Data Report

You can now use the Power BI Covid data to create reports.

In this screenshot, I pulled in the earliest date and the latest date in the dataset.

Then I built a quick table report to show the Counties, States, and Case Count by Date to ensure the data transformed as expected.