Import Data from Web To Excel Using Power Query

The Power Query is an amazing feature provided by Microsoft in Excel 2016 and higher versions. Using this feature, you can extract data from various sources to excel and then perform transformation steps on this data. To name a few, the power query feature allows you to extract data from sources like – another excel workbook or files, text files, or CSV file format. You can also import or get data or table from the web i.e. website URL to excel using this feature.

In this ExcelUnlocked tutorial on Power Query, we would learn to copy and import table from web to excel.

Why Power Query is Best to Extract Data from the Web?

The best part of the Excel Power Query feature is that you need to extract the data from the source only once. The power query instantly records the steps performed by you and stores them in the memory (connections). Now, when the source data updates, you need to simply refresh the power query. On refresh, the power query connections would rerun the same steps in sequence to extract updated data from the source.

The data and tables on websites get updated on a frequent basis.

To get an updated table from the web to excel, you can use the Excel Power Query feature. Here, you need to import data from the web to excel only once. Later on, when the data on the website changes, simply refresh the power query connection to rerun the steps.

If you are new to Excel Power Query tool, get an overview on power query in this link.

Web URL To Export Data to Excel

Let us take the below example website URL

https://en.wikipedia.org/wiki/COVID-19_pandemic_by_country_and_territory

This webpage contains details about coronavirus pandemic count by country and territory. In this URL, there are many tables containing information related to the deadly coronavirus.

Let us copy and import the table(s) from this web URL to excel using the Power Query Excel feature.

Import or Get Data From Web To Excel | Power Query

Before going through the below steps, make sure that you are connected to internet.

Copy and Import One Table From Web To Excel

To extract single table from website to excel, follow the undermentioned steps:

  • Open a new excel workbook and navigate to the ‘Data‘ ribbon tab > ‘Get & Transform Data‘ group. Then, navigate to this path: Get Data > From Other Sources > From Web.
Get Data From Web Navigation

The image above is from the “Excel Office 365” version. The look of the Power Query navigation may be different in Excel 2016 and 2019. If you are using versions prior to Excel 2016, then check out this tutorial – Install Power Query in Excel 2010 [Step by Step Guide]

From Web Dialog Box Power Query
  • As a result, excel would connect to the website and extract data, objects, and tables from the website. On successful extraction, a ‘Navigator‘ window would appear on your screen. This window would show the list of all the website tables and other objects inside a folder (where Folder Name = Website URL).
Navigator Window with Web Tables Objects
  • Select the website table that you want to import to excel. The right side of the ‘Navigator’ window would show its preview. To directly load the previewed table into excel, click on the ‘Load’ button.
Load Web Table to Excel Directly
  • As a result, excel would connect to the web URL to extract the selected table from the website and place it in a new excel worksheet.
Result of Extract Table from Website to Excel
  • Now, when the source website data gets updated, simply right click on this excel table and select the option ‘Refresh‘.
Right Click Refresh Power Query

As a result, you would notice that excel again connects to the website. Till the time the power query tool is connecting to the website, you would see the below message on the excel status bar.

Running Background Query - Status Bar

Once refresh is completed, power query would extract the latest and updated data from website to excel.

Copy and Import Multiple Tables From Web To Excel

To extract more than one tables from web to Excel, follow the undermentioned steps:

Import Data from Web To Excel
  • Navigate to the ‘Data‘ ribbon tab > ‘Get & Transform Data‘ group. Then, navigate to this path: Get Data > From Other Sources > From Web.
Get Data From Web Navigation
From Web Dialog Box Power Query
  • As a result, excel would connect to the website and extract data, objects, and tables from the website. On successful extraction, a ‘Navigator‘ window would appear on your screen. This window would show the list of all the website tables and other objects inside a folder (where Folder Name = Website URL).
Navigator Window with Web Tables Objects
  • To select multiple tables, tick the checkbox – ‘Select multiple items‘ and then start selecting the checkboxes beside the table names, as shown in the below image-
Select Multiple Tables Power Query Excel
  • Now, click on the ‘Transform Data‘ button on the bottom right corner of the Navigator window. As a result, the ‘Power Query Editor’ window would appear on your screen.
  • Simply, go to the ‘Home‘ tab and click on ‘Load and Save‘ option, as highlighted below:
Power Query Editor - Close & Load Icon

As a result, excel would load both the tables to excel in different worksheets.

With this we have reached to the end of this tutorial on extracting data from web to excel using power query.

Leave a Comment