One of the more confusing parts of Excel Power Query is how to launch it. Microsoft re-branded Power Query in Excel which now resides under the Get & Transform section of the Excel ribbon. After launching the Power Query Editor you can record steps to modify and transform data that then gets loaded into a tab of your Excel workbook.
We’ll explain the different ways to create a Power Query in Excel, how to edit them once they’ve been created and some additional tips and tricks to help you streamline and automate processes with one of Excel’s best feature sets.
Let’s jump in!
Table of Contents
How to Access and Launch Power Query in Excel
To launch Power Query in Excel, you either have to start by adding data to Power Query from data that already exists in your workbook, connect it to an outside data source, or edit a pre-existing Power Query. The options to work with Power Query are under the Get & Transform and Queries & Connections sections of the Data tab.
Because Power Query is a subset of Excel tools designed to speed up the data transformation process, any steps applied in Power Query have to be applied to existing data. Power Query does not let you enter or modify data like you can in a regular Excel spreadsheet.
This means that when you launch Excel, the first step to launch Power Query is to load data into it, or connect it to an existing source of data.
To launch Power Query based on data that already exists in your Excel workbook, go to Data, Get and Transform, and click “From Table/Range” with data selected.
As soon as you click on From Table/Range a new window will pop=up, this is the Power Query Editor.
When working in the Power Query Editor, a new ribbon appears at the top, these are all of the steps or actions that you can apply to the data. Remember, you won’t be able to edit data in this view, you will only be able to transform it with steps like, remove duplicates, replace null values, remove rows, merge with other tables etc.
Any editing of the data has to be done in the source dataset.
Launching the Power Query Editor Without Data
In Excel, you can also launch the Power Query Editor by going to the Data Tab, clicking on Get Data, and selecting “Launch Power Query Editor”. However, unless you have data to load or already loaded into Power Query all options of the screen will be grey so you won’t be able to do anything.
The one advantage of this method is that if you already have data loaded into Power Query, you can click this button to edit existing queries, but there are also easier ways to access them as we’ll explain later.
Types of Data Sources You Can Load Into Power Query
Power Query in Excel supports a wide range of data sources, A common use case is to use it to automate the transforming of data that’s already in Excel, but there are many data sources that exist which you can either connect to or import.
We recommend connecting to data as close to the source system as possible, such as a data warehouse, ERP, or even a folder of reports that haven’t been manually modified yet to get the most out of the recorded steps.
Some data sources include:
- Excel Files – You can import data from other Excel workbooks, or folders of Excel files.
- Text Files – Load data from text files, such as CSV or TXT formats.
- Databases – Connect to various databases like SQL Server, MySQL, Oracle, and others.
- Online Services – Access data from online services like SharePoint, Exchange, Azure, or Power BI
- Other Sources – Power Query also supports other sources like XML, JSON, Folder, and more.
You can see many of the data sources available, by clicking on Get Data from the Data tab and viewing the pre-made connections that re available. Options range from older technologies to newer cloud technologies.
Each of these data sources comes with its own set of options and configurations in Power Query, allowing for a tailored approach to data management and analysis. This flexibility ensures that Excel users can handle a wide array of data types efficiently, making Power Query a powerful tool in the Excel suite.
Adding Data from Power Query Into an Excel Workbook
When you are done transforming data in Excel Power Query, click on the Close & Load button on the left side of the Home Ribbon of the Power Query Editor. This finalizes all of the transformations and steps that you’ve applied to your data and will load the data back into an Excel spreadsheet.
The newly loaded data is placed into a table on a new tab of our Excel workbook. There are some other options of where to load data after it’s transformed in Power Query, but loading it back into an Excel workbook is the most common.
The other thing that might stick out is that when you have the green table of data selected that is the result of Power Query steps, a Queries & Connections panel appears on the right side of the screen.
The Queries & Connections panel of Excel keeps track of different datasets, data connections, and different Power Queries that have been created.
How to Edit an Existing Query in Excel
To edit an existing Power Query in Excel, open an Excel workbook that contains the Power Query. Navigate to the Data tab of the Excel Ribbon and click the Queries & Connections button, next to Get & Transform options. Once the Queries & Connections panel appears you can double click on a query to launch the Power Query Editor.
You can also right click on a query and select edit, along with seeing some other common options such as loading it to a data model, duplicating a Power Query, or Delete an Existing Power Query.
The primary advantage of using the Queries and Connections view is the ability to see all different Queries at once. It’s common to have multiple queries when you have multiple incoming datasets that each require data cleaning and preparation.
How to Refresh Power Query in Excel
To refresh Power Query in Excel, navigate to the Data tab of the Excel Ribbon, then click on Refresh All. Alternatively, you can right click on a Power Query Table in a workbook, or the Query Named on the Queries & Connections panel and select Refresh to refresh data more selectively.
Once a refresh is started, Excel will go out query the data from a connection or file source then go through each of the applied steps established in Power Query and output the results.
Power Query can even be refreshed using Excel Online, providing even more automation options. You can learn more from Microsoft here: How to Use Power Query in Excel for the Web. One drawback at the time of writing is that Power Queries have to be created in Excel for Desktop but we hope that will change some day soon.
Power Query is an extremely versatile part of Microsoft Excel. There are also a number of different ways to launch, edit, and load data from Power Query back into Excel. The most common is to load data using the Get Data button or to create it from a table of data already existing in your workbook even though you can also connect to many different data sources.
After applying steps to transform the incoming data, click on Close & Load to exit the Power Query Editor.
Then either right click edit on a table that’s been loaded from Power Query or right click and edit a query listed on the Queries and Connections screen.
Finally after Power Query is setup to your liking, you can choose to Refresh All or selectively refresh a single query at a time by right clicking and selecting Refresh on a table or a query.