Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

What is Corona Virus?

Corona Virus ( COVID-19) is a severe acute respiratory syndrome disease caused by the SARS-COV-2 Virus. It is first identified in Wuhan Province China in December 2019.

WHO has declared it pandemic on 11 March 2019 as it is believed to spread in 115 Countries across the Globe. As per John Hopkins University Dataset, 3M people have been confirmed effected by Corona Virus, 92k has been dead due to this deadly disease and 1 Million have been recovered.

Currently WHO has identified Europe as the epicenter of this disease as a large number of people are getting infected day by day.

Most popular Country effected by Corona Virus is Mainland China, US, India, Italy, South Korea, Iran, and Spain.

Corona Virus symptoms

Corona Virus spreads similar to Influenza and its symptoms are also similar to it. Fever, Dry Coughing, Shortness of Breath.

At Present, No Vaccine has been found for Corona Virus. It is believed that the Corona Virus spreads in Colder Environment and cannot withstand hot Climate.

Precautions from Corona Virus

Corona Virus spreads in dirty moisture places as at those places have  lower temperature and moisture which is required for its growth.

Like all Viruses, Corona Virus also needs a medium for its propagation. Viruses are idle at outside places and act as parasites when they are inside the human a body so we have to prevent ourselves from infected people by avoiding touch and using Namaskar for Salutation, maintain a distance of 1 meter from people, Avoid Public Gathering, Keeping your surroundings clean.

Prevention from Corona Virus is essential at the point of time. Good Hygiene Practice such as Washing your Hand frequently, Covering your mouth with a tissue while coughing or sneezing can only prevent people from this disease.

Many people around the world have been recovered and also the mortality rate of this disease is only 3.4 % so people with a strong immune system can overcome but old people, pregnant women and children need to take extra care.


 Corona Virus Report - Microsoft Power BI



 

How to Import Corona Virus Dataset provided by John Hopkins University at Git Hub?

John Hopkins University, Baltimore, Maryland, US  has provided dataset which includes confirmed cases, Deaths, and Recovered cases captured all over the World.

Dataset is available at the link here.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

Data from these CSV files can be imported into Microsoft Power BI using Web Connector available in Microsoft Power BI Desktop.



The next step is to use a web URL used to connect to GitHub and import the data from the CSV file.

Most important is to use the correct URL so that you can get data from CSV. Kindly your below the link to access the dataset.

To access Confirmed cases file, Please use below link-

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv

To access deaths cases file.  Please use below link -

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Deaths.csv

To access recovered cases, Please use below link-

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Recovered.csv

Transform COVID 19 GitHub Data Set in Power Query


Step 1: Open the Advanced Editor, Remove Columns=XX in the Path link after Delimiter option. After removing the Advance editor Code will look like this.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub
 Step 2: Click on the option in Ribbon "Promote First Row as Header"

Earlier  - All the Header elements were placed in the first Row of the Table. So it was essential to click on the option Promote First Row to Header.
Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

Post-
Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

Step 3 - As you can see that Dates are placed as the header in the table but we have to bring dates in a single Column and also confirmed cases value in the single column.

 In order to achieve this, we have to select all the columns except the date column and select the "Unpivot others column" in the right-click menu.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub



Step 4- Change the Attribute column name to Date and change its data type. Also, change the column name value to Confirmed.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub


Do the same steps for the other two Queries. Your Screen will look like this-

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub


Prepare the Data Model for COVID 19 GitHub Dataset using Power BI -DAX

Step 1- Prepare a Date Table using DAX. Go to the Modelling Tab and click on the New Table option in the Ribbon.

Now you will get the screen to type your DAX Query. Copy the below code and paste it to prepare the Date Table using DAX

Date Dimention =
GENERATE (
    CALENDAR ( DATE ( 2020, 1, 22 ), DATE ( 2022, 12, 31 ) ),
    VAR currentDay = [Date]
    VAR day =
        DAY ( currentDay )
    VAR month =
        MONTH ( currentDay )
    VAR year =
        YEAR ( currentDay )
    RETURN
        ROW ( "day", day, "month", month, "year", year )
)


Step 2 - Create a Calculated Column which is a combination of Country and State. Use the below DAX Code

Country/State = IF ( IS BLANK 
( Confirmed[Province/State] ), 
 Confirmed[Country/Region], 
 COMBINEVALUES ( "/", Confirmed[Country/Region], 
Confirmed[Province/State] ) )

Later, You can go to the modeling tab and click on New Table and type the below code for a new table with Country/State Column which will help us to filter all the table Country Wise.

ALL(Confirmed[Country/State])

After you have created the Country Column in all the tables. Make Relationship in the Data Model as it is shown below

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub


After this, You can design your report as per your need. If you want to learn any other functionality used in the report then please let me know in the comment section.


Thank You.



You may also like:

Top Websites for Free Public Data Sets for Machine Learning and Data Science Project

DAX Variables in PowerBI-Subtracting Prior Value from Current Value of a Column

Power Query M Language-Convert Seconds to HH:MM: SS Format in Power BI

Microsoft Power BI Desktop: Difference between PowerQuery, Power Pivot and Power View

As per Gartner Magic Quadrant Analytics and Business Intelligence platform Report, Microsoft Power BI leads in 2020


Corona Virus Breakout | Microsoft Power BI + Github Dataset

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

What is Corona Virus?

Corona Virus ( COVID-19) is a severe acute respiratory syndrome disease caused by the SARS-COV-2 Virus. It is first identified in Wuhan Province China in December 2019.

WHO has declared it pandemic on 11 March 2019 as it is believed to spread in 115 Countries across the Globe. As per John Hopkins University Dataset, 3M people have been confirmed effected by Corona Virus, 92k has been dead due to this deadly disease and 1 Million have been recovered.

Currently WHO has identified Europe as the epicenter of this disease as a large number of people are getting infected day by day.

Most popular Country effected by Corona Virus is Mainland China, US, India, Italy, South Korea, Iran, and Spain.

Corona Virus symptoms

Corona Virus spreads similar to Influenza and its symptoms are also similar to it. Fever, Dry Coughing, Shortness of Breath.

At Present, No Vaccine has been found for Corona Virus. It is believed that the Corona Virus spreads in Colder Environment and cannot withstand hot Climate.

Precautions from Corona Virus

Corona Virus spreads in dirty moisture places as at those places have  lower temperature and moisture which is required for its growth.

Like all Viruses, Corona Virus also needs a medium for its propagation. Viruses are idle at outside places and act as parasites when they are inside the human a body so we have to prevent ourselves from infected people by avoiding touch and using Namaskar for Salutation, maintain a distance of 1 meter from people, Avoid Public Gathering, Keeping your surroundings clean.

Prevention from Corona Virus is essential at the point of time. Good Hygiene Practice such as Washing your Hand frequently, Covering your mouth with a tissue while coughing or sneezing can only prevent people from this disease.

Many people around the world have been recovered and also the mortality rate of this disease is only 3.4 % so people with a strong immune system can overcome but old people, pregnant women and children need to take extra care.


 Corona Virus Report - Microsoft Power BI



 

How to Import Corona Virus Dataset provided by John Hopkins University at Git Hub?

John Hopkins University, Baltimore, Maryland, US  has provided dataset which includes confirmed cases, Deaths, and Recovered cases captured all over the World.

Dataset is available at the link here.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

Data from these CSV files can be imported into Microsoft Power BI using Web Connector available in Microsoft Power BI Desktop.



The next step is to use a web URL used to connect to GitHub and import the data from the CSV file.

Most important is to use the correct URL so that you can get data from CSV. Kindly your below the link to access the dataset.

To access Confirmed cases file, Please use below link-

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv

To access deaths cases file.  Please use below link -

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Deaths.csv

To access recovered cases, Please use below link-

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Recovered.csv

Transform COVID 19 GitHub Data Set in Power Query


Step 1: Open the Advanced Editor, Remove Columns=XX in the Path link after Delimiter option. After removing the Advance editor Code will look like this.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub
 Step 2: Click on the option in Ribbon "Promote First Row as Header"

Earlier  - All the Header elements were placed in the first Row of the Table. So it was essential to click on the option Promote First Row to Header.
Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

Post-
Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub

Step 3 - As you can see that Dates are placed as the header in the table but we have to bring dates in a single Column and also confirmed cases value in the single column.

 In order to achieve this, we have to select all the columns except the date column and select the "Unpivot others column" in the right-click menu.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub



Step 4- Change the Attribute column name to Date and change its data type. Also, change the column name value to Confirmed.

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub


Do the same steps for the other two Queries. Your Screen will look like this-

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub


Prepare the Data Model for COVID 19 GitHub Dataset using Power BI -DAX

Step 1- Prepare a Date Table using DAX. Go to the Modelling Tab and click on the New Table option in the Ribbon.

Now you will get the screen to type your DAX Query. Copy the below code and paste it to prepare the Date Table using DAX

Date Dimention =
GENERATE (
    CALENDAR ( DATE ( 2020, 1, 22 ), DATE ( 2022, 12, 31 ) ),
    VAR currentDay = [Date]
    VAR day =
        DAY ( currentDay )
    VAR month =
        MONTH ( currentDay )
    VAR year =
        YEAR ( currentDay )
    RETURN
        ROW ( "day", day, "month", month, "year", year )
)


Step 2 - Create a Calculated Column which is a combination of Country and State. Use the below DAX Code

Country/State = IF ( IS BLANK 
( Confirmed[Province/State] ), 
 Confirmed[Country/Region], 
 COMBINEVALUES ( "/", Confirmed[Country/Region], 
Confirmed[Province/State] ) )

Later, You can go to the modeling tab and click on New Table and type the below code for a new table with Country/State Column which will help us to filter all the table Country Wise.

ALL(Confirmed[Country/State])

After you have created the Country Column in all the tables. Make Relationship in the Data Model as it is shown below

Corona Virus Breakout - Get Updated Status through Microsoft Power BI and GitHub


After this, You can design your report as per your need. If you want to learn any other functionality used in the report then please let me know in the comment section.


Thank You.



You may also like:

Top Websites for Free Public Data Sets for Machine Learning and Data Science Project

DAX Variables in PowerBI-Subtracting Prior Value from Current Value of a Column

Power Query M Language-Convert Seconds to HH:MM: SS Format in Power BI

Microsoft Power BI Desktop: Difference between PowerQuery, Power Pivot and Power View

As per Gartner Magic Quadrant Analytics and Business Intelligence platform Report, Microsoft Power BI leads in 2020


No comments