In this post, will look at importing data from web and then analyze the data captured after applying few simple modelling steps using Power Query in Power BI.
For this post, I have used wiki’s web url that has each country’s population on the same date and month in 2018 and 2019. Will use the data from the web table and create a visualization that shows the population by continent in 2018 and 2019. Let’s look at the steps involved in doing this.
1) Open the Power BI desktop application and import data from web. To do this, click on File -> Get Data -> Web.
2) After the first step, ‘From Web’ dialog box will open. Provide the source url and click ‘OK’. When asked for access settings, select ‘Anonymous’ and click ‘Connect’.
3) Now, ‘Navigator’ dialog box will open to select the table from the web page. Select the ‘Countries and areas ranked by population in 2019’ and click on ‘Load’.
4) Data will be loaded after the above step. However, data retrieved has to be cleaned and the data types have to be fixed before it’s used for reporting. Click on Home -> Edit Queries to handle the data retrieved.
5) You could now see the data retrieved. Although the headers on the table are shown correctly, first data row in the table shows the header information. You could now either delete the first data row or use the first data row as headers instead. This step will look at using the first row as headers. To do this, click on Home -> Use First Row as Headers from the ribbon.
6) In the above step, first row was promoted as header. Now the first row’s ‘Country or area’ is ‘World’, which holds the total population of all countries, which is not required in this context. Therefore, we will remove this row from the table. To do this, click on Home -> Remove Rows -> Remove Top rows from the ribbon. This will open a ‘Remove Top Rows’ dialog box, enter 1 under ‘Number of rows’ to remove the first row only and click ‘OK’.
7) Let’s look at few other data modelling steps.
a. Modifying the data type of the ‘Rank’ column from ‘Text’ to ‘Whole Number’: select the ‘Rank’ column and click on Home -> Data Type -> Whole Number from the ribbon.
b. Rename the column header ‘Population (1 July 2018)’ to ‘Population_2018’: select the ‘Population (1 July 2018)’ column and click on Transform -> Rename from the ribbon, this will make the column header editable, change the column header to ‘Population_2018’. Similarly, rename the column header ‘Population (1 July 2019)’ to ‘Population_2019’.
c. Remove the unwanted ‘Change’ column from the table: select the ‘Change’ column and click on Home -> Remove Columns -> Remove Columns from the ribbon.
8) Now, apply all the changes made to the data table for reporting. To do this, click on Home -> Close & Apply from the ribbon.
9) On the ‘Report’ page, select matrix visualization, select ‘UN continental region’ into the ‘Rows’ section and ‘Population_2018’ and ‘Population_2019’ into the ‘Values’ section to report the population data by continent in 2018 and 2019 as planned.
Hope this post helps you!
Simple SQL BI blogs 