Skip to content
Mike Evans-Larah By Mike Evans-Larah Software Engineer III
Power Query - Where can you use it? - Microsoft 365

What is Power Query?

Power Query is data processing / transformation engine capable of performing a wide variety of ETL tasks and allowing users to "mash-up" data across sources. It has a graphical interface for building up the queries and transformations. Under the covers, it uses the Power Query M formula language for expressing its queries, which can be edited directly for advanced queries. There are hundreds of connectors that can used for reading data across different sources.

The engine began life as part of the Power Pivot extension for Excel, used for getting and transforming data to use in Pivot Tables.

Since then, it has been integrated across many more applications in the Microsoft ecosystem, and we'll take a look at them in this series of posts. In this post, we look at Microsoft 365 integrations.

Microsoft 365

Excel

The original integration for Power Query, and the only integration in the Microsoft 365 suite.

The best hour you can spend to refine your own data strategy and leverage the latest capabilities on Azure to accelerate your road map.

Whereas in older versions of Excel it required installing an add-in, Power Query is now built-in to Excel for Windows, Excel for Mac, and Excel for Web.

To get started, use the 'Get Data' button under the 'Data' tab of the ribbon.

Screenshot of 'Get Data' menu expanded in Excel

From here, you can select a datasource type to start building a query using a wizard, or launch the Power Query Editor directly.

Screenshot of the Power Query Editor in Excel

Once you have finished building your query, the data can then be loaded into a worksheet as a table.

Screenshot of an example 'orders' table loaded into an Excel worksheet

When it comes to refreshing the data, there are a few options:

  • Manually refresh the data using the 'Refresh' button
  • Refresh the data when the workbook is opened (Query > Properties > Refresh Control > 'Refresh when opening file')
  • Refresh the data periodically (Query > Properties > Refresh Control > 'Refresh every X minutes')

Screenshot of query refresh properties in Excel

Mike Evans-Larah

Software Engineer III

Mike Evans-Larah

Mike is a Software Engineer at endjin with over a decade of experience in solving business problems with technology. He has worked on a wide range of projects for clients across industries such as financial services, recruitment, and retail, with a strong focus on Azure technologies.