Power Query is an extremely useful feature that allows us to:

  • connect to different data sources, like text files, Excel files, databases, websites, etc…
  • transform the data based on report prerequisites.
  • save the data into an Excel table, data model, or simply connect to the data for later loading.

The best part is that if the source data changes, you can update the destination results with a single click; something that is ideal for data that changes frequently.

This is analogous to recording and executing a macro.  But unlike a macro, the creation and execution of the back-end code happen automatically.

If you can click buttons, you can create automated Power Query solutions.

Featured Course

Master Excel Power Query – Beginner to Pro

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.
Learn More
Power Query Course cover

Example #1 Import Spot Prices for Petroleum from a Website to Excel

The first step is to connect to the data source.  For this example, we will connect to the U.S. Energy Information Administration.


The information we need is in a table that is part of the overall webpage.

Website with table of wholesale spot petroleum prices

In the “old days”, we would transfer the information from the website into Excel by highlighting the webpage table and copy/paste the data into Excel.

If you’ve ever done this, you know what a hit-or-miss proposition this can be.  It’s the 50/50/90 Rule: if you have a 50/50 chance of winning, you’ll lose 90% of the time.

Even if you were to successfully transfer the information into Excel, the information is not linked to the webpage.  When the webpage changes, you will need to recopy/paste (and potentially fix) the updated information.

  1. Begin by copying the URL from the webpage (assuming you are previewing the page in a browser). If not, you can type it into the next step’s URL prompt.
  2. Select Data (tab) -> Get & Transform (group) -> From Web.
Excel interface showing the Data tab & Get & Transform group of tools, with From Web highlighted.
  1. In the From Web dialog box, paste the URL into the URL field and click OK.
Power Query interface showing where to populate the URL of the website for import

The Navigator window displays the components of the webpage in the left panel.

Power Query Navigator interface displaying page components in Table View

If you want to ensure you are on the correct webpage, click the tab labeled Web View to get a preview of the page in a traditional HTML format.

Power Query Navigator interface displaying page components in Web View

It is unlikely that the listed components on the left will be presented with obvious names as to which item goes to which webpage component. You may need to click from one item to the next, previewing each item in the right-side preview panel, in order to determine which belongs to the desired table.

Power Query Navigator interface

If the data does not require any further transformations, you can click Load/Load To… to send the data directly to Excel.  This will allow you to select the destination of the results data, such as a table on a new or existing worksheet, the Data Model, or create a “Connection Only” to the source data.

  1. In the Navigator dialog box, select the arrow next to Load and click Load To…
Power Query Navigator interface showing Data Load options
  1. In the Import Data dialog box, select “Existing worksheet” and point to a cell on your desired destination worksheet (like cell A1 on “Sheet1”).
Import Data window displaying locations where the data can be loaded

The result is a table that is connected to a query.  The Queries & Connections panel (right) lists all existing queries in this file.

Excel worksheet with a table of data and Queries & Connections side pane

If you hover over a query, an information window will appear giving you the following information:

  • a preview of the data
  • the number of imported columns
  • the last refresh date/time
  • how the data was loaded or connected to the Excel file
  • the location of the source data
Window showing query details, including preview, number of columns, refresh date, load status, and data sources

Although the data looks correct, we have some structural issues with the results that will cause problems with further analysis.

Excel spreadsheet with a table of data and arrows pointing to empty cells.

The empty cells in the Product column will cause problems when sorting, filtering, charting, or pivoting the data.

We need to make some adjustments to the data.

  1. Double-click (or right-click and choose Edit) the listed query to activate the Power Query Editor.
Power Query Editor interface

We want to fill down the listed products into the lower, empty cells of the Product column.

  1. In the Power Query Editor, select the Product column and click Transform (tab) -> Any Column (group) -> Fill -> Down.
Power Query Editor interface with the Transform tab and Fill Down feature highlighted

Nothing happened.

Power Query Editor showing that the Fill Down step did not work as expected.

The reason the product names failed to repeat down through the empty cells is that Power Query did not interpret the cells as empty.  There may be some artifact from the webpage that exists in the cell that we can’t see.

We will replace all the “fake empty” cells with null values.  This should allow the Fill Down operation to work as expected.

  1. Select the Changed Type step in the Query Settings panel (right).
Fragment of Power Query Editor - Query Settings pane, with the Changed Type step highlighted.
  1. Select the Product column and click Transform (tab) -> Any Column (group) -> Replace Values.
Fragment of Power Query Editor with the Transform tab and Replace Values highlighted
  1. Tell Power Query that you wish to insert this new step into the existing query by clicking Insert.
Dialog window asking if you are sure you want to insert a step.
  1. In the Replace Values dialog box, leave the “Value To Find” field empty and type “null” (no quotes) in the “Replace With” field. Click OK when finished.
Replace Values dialog window.

If you select the previously created “Filled Down” step at the bottom of the Query Settings panel you will see the updated results of the query.

Power Query Editor showing successful Filled Down step.
  1. Update the query name to “Spot Prices”.
Fragment of the Power Query Editor showing the Query Settings pane with Query Name highlighted.
  1. Click the top part of the Close & Load button at the far-left of the Home

If we were to graph the data, and the data were to change, we can refresh our graph by clicking Data (tab) -> Queries & Connections (group) -> Refresh All or right-click on the data and select Refresh.

Excel spreadsheet with table of data and context menu with Refresh highlighted

Query Options

There are some controllable options available by selecting Data (tab) -> Queries & Connections (group) -> Refresh All -> Connection Properties…

Excel workbook showing the Data ribbon with Refresh options expanded to show Connection Properties.

Some of the more popular options include:

  • Refresh the data every N number of minutes
  • Refresh the data when opening the file
  • Opting for participation during a Refresh All operation
Query Properties window with Refresh options highlighted.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Example #2 Import Weather Forecast for the Next 10 Days

Imagine you work at the front desk of a popular hotel in Seattle.  As a customer service, you wish to supply your guests with a printout of the weather forecast for the next 10 days.

This is something that needs to be printed every day where each successive day looks at its next 10 days.

  1. We start by searching for a website that can supply a 10-day forecast for Seattle.
Google search for 10-day forecast for Seattle.
  1. We take the first offer in the search results that takes us to weather.com.
Table showing 10 Day Weather for Seattle, WA.
  1. Now that we have the web link to the 10-day forecast for New York City, we will copy and paste it into a web query in Excel (Data (tab) -> Get & Transform (group) -> From Web).
  2. Select the table from the left side of the Navigator window.

We can see from the preview that there are some columns towards the right that we are not interested in and the column headers have shifted.

Power Query Navigator window.
  1. In the Navigator window, select Transform Data to load the forecast into Power Query.
  2. Begin editing the data by right-clicking on the header for the “Day” column and select Remove.
Fragment of Power Query Editor showing Remove Column feature.
  1. Select the last 3 columns (“Wind”, “Humidity”, and “Column7”) and remove them as well.
  2. Rename the remaining 3 columns.
    • Description -> Day
    • High / Low -> Description
    • Precip -> High/Low
  1. Rename the query “SeattleWeather”.
  2. On the Home tab, select the lower-part of the Close & Load button and click Close & Load To… and select Existing Worksheet from the Import Data dialog box. Click OK when complete.
Fragment of Power Query Editor showing the data table and the Query Settings pane with applied steps.

We now have the weather forecast for the next 10 days.

Excel spreadsheet with a table of weather data.

Each day, we only need right-click the table to refresh the information.

Impressing the Boss

We really like the idea seen on the original weather.com website that displays a raincloud emoji for days that are expecting rain.

Table column showing days of the weak and pictograms indicating the weather.

To bring a bit of fun to our report, we will have Excel display an umbrella emoji for any day that is forecasting rain or showers.

NOTE: This creative bit of Excel trickery is brought to us by our good friends Frédéric Le Guen and Oz du Soleil.  Links to their blog and video detail various uses of this trick can be found at the end of this post.

The first step is to add the umbrella emoji.  We will access the built-in Windows emoji library.

  1. On the keyboard, press the Windows key and the period to display the emoji library.
Windows emoji picker.
  1. Type the word “rain” to filter the emoji library to rain-related emojis.
  2. Select the umbrella with raindrops.
Excel spreadsheet with an umbrella emoji inserted into a cell from the Windows emoji picker.
  1. Highlight the umbrella emoji in the Formula Bar and press Copy (CTRL-C) then Enter.
  2. Double-click the “SeattleWeather” query to launch the Power Query Editor.

The next step is to create a new column that adds the umbrella emoji for any row that contains the words “rain” or “shower” in the Description column.

  1. Select Add Column (tab) -> General (group) -> Conditional Column.
Power Query Editor ribbon with the Add Column tab and Conditional Column highlighted.
  1. The name of the new column is “Be Equipped” and the logic is “if the column named ‘Description’ contains the word ‘rain’ then display the umbrella emoji”. (Paste the umbrella emoji into the Output)
  2. Click the Add Clause button to create a second condition.
  3. The second description’s logic is “else if the column named ‘Description’ contains the word ‘shower’ then display the umbrella emoji”. (Paste the umbrella emoji into the Output)
Power Query Add Conditional Column dialog window.
  1. Click OK to add the new conditional column.
Fragment of Power Query Editor with the data table. Column with umbrella emojis highlighted.
  1. Drag the header for “Be Equipped” so the new column lies between the “Description” and “High/Low” columns.
Fragment of Power Query Editor with the data table. Column with umbrella emojis highlighted.
  1. Close & Load the updated query back into Excel.
Excel spreadsheet with table of data.

Tomorrow, you only need right-click the table to load the updated 10-day weather forecast.

Frédéric Le Guen’s blog post on adding emojis to your reports

Oz’s video – Emojis, Excel, Power Query & Dynamic Arrays

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.