The good thing with Power Query is that there are multiple ways to pull in data.
Import Web Data Using Power Query
One of the way is to get data from the internet by using the importing web data feature. Let us see how we can parse a webpage and use it for Power Query.
Step 1: Get Data from Web
To get any data from Web into Power Query Editor, go to-
Home > Get Data > Web
Step 2: Enter URL to connect Web page
After you go to the Web option in getting Data, it will take you to a window where you need to provide the link address to connect it to the Power Query as shown in the picture below.
When you click on OK, the Web data is loaded into Power Query and you can choose any file from the folder displayed in the Navigator page. Here, we have selected the Rank order sheet as shown in the below picture.
This sheet is then displayed in Power Query Editor as shown below.
Step 4: Remove Top Rows
As you can see in the above picture, the top 6 rows do not contain any data and since should be removed. To remove these rows, go to-
Home > Remove Rows > Remove Top Rows
This will display a window where we need to provide the number of top rows we want to remove. We have provided 6 no as shown in below picture.
The picture below shows the data after removing the top 6 rows.
Step 5: Remove Selected Column
The first column of our data contains null values. Therefore, it must be removed. To remove this column, right click on it and select the Remove option as shown below.
Step 6: Use First Row as Header
Use the first row of your data table as headers. To do that, go to-
Transform > Use First Row as Headers > Use First Row as Headers
The picture below shows the final data. This is how you can connect any data from the web directly into Power Query and make little transformations as shown above to use that data.
You can learn more :
- Top Ways to Import Data From XML
- Methods to Import Data From CSV
- Learn How to Filter Records Using Power Query
- How to Unpivot Data Using Excel Power Query