Excel Query Editor Tutorial

Power query displays the import data dialog box.

Excel query editor tutorial. To display the query editor dialog box connect to a data source and click edit query in the navigator pane or double click a query in the workbook queries pane. The query editor also opens whenever you connect to a data source create a new query or load an existing query. The edit button will take you to the query editor where you can transform your data before loading it. In this tutorial youll use excels power query experience to create a query that imports data from a web page.

To connect to a data source see import data from external data sources. As part of this process youll navigate across tables available on a web page and apply data transformation steps to display just the data you want to see. With power query 210 and later you can share and manage queries as well as search data within your organization. Power query has its own editor where you can get the data perform all the steps to create queries and then load it to the worksheet.

I will extract data from an external excel file called mock dataxlsxin this file i have a list of malefemale mock up customers. In this simple example we will bypass the editor and go straight to loading the data into excel. If we are using excel 2016 we will click on get external data directly from the data tab figure 2 microsoft query wizard. Pull data into power pivot from new data sources such as xml facebook and file folders as refreshable connections.

Below is the first look of the editor which you will get when you open it. Go to ribbon data get data from file from workbook. How to create a microsoft query in excel. Create a query from your facebook likes that render an excel chart.

To import a single data source from an excel workbook with power query follow these 6 steps. Using the microsoft query tool in our open excel document we will click on data in the ribbon tab and select from other sources. In this step by step tutorial i will show you how to create an microsoft query to extract data from either you current workbook or an external excel file. Identify the source workbook and double click on it.

Click on any individual query to activate it for viewing or editing. Pressing the load button will load the data into tables in new sheets in the workbook. Expand the queries list by clicking on the arrow to the top left of the data preview. Power query data sources.

Power query uses a dedicated window called the query editor to facilitate and display data transformations. You can open the query editor by selecting launch editor from the power query ribbon. Once the power query editor opens we can edit any query which exists in the workbook.