In this tutorial, I will show you how to build this Power BI report from a web API, write simple DAX to create measures, add custom visuals and schedule the refresh time. The main topics are:
- Get Data in JSON from from a real-time web API to Power BI Desktop and Transform the JSON data to a tabular format
- Create columns and measures for Fatality and Recovery rates
- Custom visuals
- Publish to the Power BI service
- Schedule the data refresh time
Get Data
For this report we will get data from a web API. One good data source is the website www.trackcorona.live created by students at Stanford, University of Virginia, and Virginia Tech. Their data set is aggregated from over 30 sources and is updated every 15 minutes. Getting access to the data is done through either an API GET request or by downloading a CSV file. For this report we will use the GET api option and use the following link: https://www.trackcorona.live/api/countries which returns the data in a JSON format.
In Power BI we will click on the Get data button, choose the Web option and paste in the link. Because the data comes in a JSON format, it needs to be converted into a Table. On the Power Query Editor click the Into Table button. Then click List and again click on the Into Table button and then OK on the pop-up window.
The next step is to choose the columns to be loaded into the model. Click on the button to the right of Column1 and select the columns. For this report I will select the following columns:
- Location
- Confirmed
- Dead
- Recovered
Once the columns have been loaded, now it is time to change the columns names and the data type. For the location column, I renamed it Country and left the data type as text whereas for the 3 other columns. Confirmed, Death and Recovered I changed the data type to whole number. Once done click on Close and Apply.
Create new columns and measures
The next step is to create several new calculated columns to be displayed on the report.
One metric we need on the report is the total number of Active cases. In order to populate this column, the first step is to sum up the total Death cases with the Recovered cases. On the Fields tab we create a column called Death_Recovered with the following formula:
Death_Recovered = countries[Dead]+countries[Recovered]
The next step is to create the Active cases where we subtract from the Confirmed cases the Death and Recovered cases using the following formula:
Active = countries[Confirmed]-countries[Death_Recovered]
Two other metrics to include in the report are the Fatality and Recovery rate, i.e. what is the death and recovery percentage?
In order to get the Fatality rate, we divide total Death cases with total Confirmed cases using the following formula:
Fatality Rate = divide(sum(countries[Dead]), sum(countries[Confirmed])
For the Recovery rate we divide total Recovered cases with total Confirmed cases using the following formula:
Recovery Rate = DIVIDE(Sum(countries[Recovered]), SUM(countries[Confirmed])
Remember to click the percentage symbol under Formatting.
Create visuals
Starting from the top, I added 3 Gauges for Confirmed, Recovered and Death cases. The process of creating them is straightforward, you choose the Gauge visual under Visualizations and drag from the Fields the corresponding metric.
The center piece is the Map visual for the Active cases by Country. We start by dragging the Country field to Location and Active to the Size. Optionally we can also drag the Confirmed, Death and Recovered fields under Tooltips so that when hovering over a bubble we get this extra information.
On the bottom I have added two Clustered column charts for the Confirmed and Death by Country fields and a Stacked bar chart for the Recovered by Country fields.
On the left side I added a logo, a Slicer and a Donut chart. For the Slicer, I dragged the Country field. The slicer is connected with all the visuals and allows for comparisons between countries.
Because the Fatality and Recovery Rates are percentages, I chose a Donut chart and changed the colors so that they match the other charts.
Publish to Power BI
After checking all the visuals and testing the functionality of the slicer it is time to Publish to the Power BI Service. We press the Publish button and choose the destination. If you want to publish your report in a specific workspace, you should create a new workspace on the Power BI Service.
Scheduled refresh
The last step is to schedule a refresh. To do this, navigate to the workspace where you saved the Report and under Datasets click on the Schedule refresh button. Choose the desired Refresh frequency and hit Apply.
