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.
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.
From here, you can select a datasource type to start building a query using a wizard, or launch the Power Query Editor directly.
Once you have finished building your query, the data can then be loaded into a worksheet as a table.
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')
