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.
Updates to Original Blog Post
Since my first publication of this blog. The 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:
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
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.