Build Power BI Covid Reports from GitHub Data

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 the Johns Hopkins Covid GitHub project that is collecting data around Covid-19 on cases based on countries from around the world.  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 19 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.  Let’s get started building a Power BI Covid report by connecting to the Covid Github data.

Updates to Original Blog Post

Since my first publication of this blog, the Johns Hopkins Covid GitHub data file name and formats have changed.  Read our new blog to update your existing Power BI desktop file.  In addition, the file names and formats could continue to change.  This new blog covers how to modify your Power BI Desktop file to point to another location or file name.

CDO Advisors Microsoft Power BI Solutions:

SDOH & COVID – Custom SDOH & COVID Dashboard Solution

BI as a Service – Low Monthly Fees, Pay Per Month Per User

Executive Dashboards – Get Dashboards in 4 Weeks

Healthcare Dashboard Proof of Concept – SEE your data visualized

Microsoft Power BI Demo – Compare your current reporting to Power BI

Power BI Quick Start Solutions – Ready to start or enhance your Power BI?

Train Your Team – Remote Power BI Developer and End User Training

Use Power BI for Finance Teams – Learn how Power BI can improve operations

Virtual Chief Data Officer – Confused by all your data?  We can help

What is Health Informatics
Access Covid data in 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

Power BI Web Data Connection String Examples

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.

CDO Advisors Power Query Example
CDO Advisors Power BI Example

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.

Are you interested in a custom solution that shows the impact of your local community or your members in your health plan?  Contact us today for our Social Determinants of Health COVID custom member dashboard.  Watch the video to learn more.