Creating Your First Customized COVID Dashboard in Power BI — A Step-by-Step Guide
It has been more than a year since the outbreak of COVID, and the situation keeps up and down. We always want to keep an eye on the numbers and check how things going: are things getting better? when could be the next overseas travelling? when will the quarantine end? To track the trends and get customized visualisations, we can easily do it with Power BI. In this article, I will show you how to connect your Power BI dashboard to the data from GitHub, how to transform and clean the data in Power Query Editor, and how to create charts/graphs.
Prerequisites:
- Power BI Desktop
- A Power BI account (if to publish the dashboard)
Step 1: Find your data
The data we will use for the dashboard is from a repository in Github, check this link to the repository, and you will be able to see the raw data in csv format.
To be able to connect your Power BI to the data, we need to click the “Raw” button, and then copy and paste the URL from the page, which should start with “raw.github…….”. Here’s one of the links we will use for the dashboard if you would like to have a look at:
Step 2: Connect your Power BI to the data
Power BI enables a range of different ways to add data such as Excel, csv, SQL, etc. For the current dashboard, we will use the data from a web page in GitHub, we select Web from Get Data.
Then we paste the URL we got from the first step, which will automatically start loading data to the Power BI file.
You are more than welcome to load the data first and then come back to edit it or just click “Transform Data” which will lead to the “Power Query Editor” window where you can tidy up the data before moving to visualisation.
Step 3: Tidy up your data
Power BI is easy to start with compare to Python or R if you want to develop a fancy dashboard as you can basically clean data and do visualisations with no coding. Now I will quickly show you how to edit the data in the Editor window.
One of the common process to start with is “Use First Row as Headers”. No matter where you load your data from, it happens often that Power BI fail to identify the first row asthe header. Instead of writing any codes, click the button at thetop (highlighted in blue), then it automatically changes the first row to the headers. What even better with Power BI is on the right under the “Applied Steps”, you can easily see all the changes you have made with the data and you can always go back to make further changes.
One key thing that I think plays an important role in the data cleanning process is to observe the data set and see how to transform it to cater for your later visualisation needs. Here, by a quick look at the data, you may get an idea that this data set consists of three types of information: 1) Location — country / region / state / province; 2) Latitude and longitude; and 3) Covid data by date. To simply the structure, I will grab the Location and Lat&Long data in one table, then Location and Covid data in the second one.
To achieve this, duplicate the table and delete irrelevant columns. Then you will get two tables: one with four columns — country / region, state / province, latitude and longitude, and the other one — country / region, state / province, all columns with data by date.
The next thing I will do is to unpivot all the “date” columns. As you can see that from the fifth column, each column lists the data for the specific date which will add complexity and difficulties to the visualisation. To make things easier, we would like to unpivot all “date” columns. To achieve this, select the columns you would like to be pivotted on, and then right click, select “Unpivot Other Columns”.
Magic happens, you are getting the data nearly there ready to be visualised!
One last few things we need to do are: to rename the column to date if you want, and to change the data type from “Text” to “Date” by clicking the left some button next to the column name. One tip here is that the date data at the moment is formatted in the US format for the date (DD/MM/YYYY), if the date format for your computer/laptop system is different from the US format, make sure you choose “Use Locale” to apply the new date formatting.
Click the “Close & Apply” button at the top left to save all the changes you have made in the Editor window and apply to the visualisation window.
Step 4: Create your charts & graphs
It is exciting that we can drag and drop, and change some setting if necessary to get our visualisations!
Below is the graph I created using the line chart, with the “Attribute” (date) as the axis, the value as the values, and Country/Region as the legend.
Conclusion:
It’s your turn now, try different types of visualisations with the cleaned data to get yourself familiar with those visuals.
To sum up, to create your Power BI dashboard, first connect your Power BI to the data source, then the most important step from my point of view — clean and transform the data, and once the data is ready it’s time to create your graphs.
Ping me on Linkedin if there’s any questions or just say a big Hi 🤪!
