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.
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.
Old File for reference only…
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.