The most useful features of Excel’s Power Query are the ability to connect easily to different types of data sources and then manipulate the data.
Links to topics on this page
- Benefits of using Power Query
- Video — Step 1: connect to an Excel workbook with Power Query
- Method — Step 1: Connect to a workbook with Power Query
- Video — Step 2: add an index a data table with Power Query
- Method — Step 2: add an index to a data table with Power Query
- Related topics
The benefits of working with a dataset in Power Query are that you can:
- create a connection without having to import the whole datatable (save time loading a large dataset),
- work with a subset of the data,
- build multiple data queries step by step,
- repeat the data query on different datasets,
- combine data sets from several files or data sources into one data table,
- maintain the raw data file untouched,
- refresh data queries via the data connection when the data source is updated.
Note: Power Query is known as ‘Get & Transform’ in Excel 2016.
Step 1: Connect to an Excel workbook using Power Query — start from Get Data | From File | From Workbook
Method (shown in video above) — Step 1: connect to data using Power Query
- From the ‘Get data‘ section of the ribbon, click on Get Data | From File | From Workbook.
- In the ‘Import Data‘ window, browse to the Excel workbook (.xlsx) file that contains the data to connect to.
- Select the file and click ‘Import‘.
- A Navigator window opens which lists the worksheets and tables contained in the workbook.
- Select the worksheet or table to import.
- From the ‘Load‘ dropdown button, select ‘Load to…‘.
- The ‘Import data’ dialog box opens, and select ‘Only Create Connection‘.
Step 2: Add an index to a data table with Power Query
Method (shown in video above) — Step 2: add an index to a data table using Power Query
- When a Power Query connection is made to a dataset, as in Step 1 above, the query will appear in the ‘Queries & Connections‘ pane on the right-hand side of the Excel window.
- You can build a second query based on this first query, by right-clicking the query block, and selecting ‘Reference‘.
- A new ‘Power Query Editor‘ window opens.
- To add an index, click on the ‘Add Column‘ tab in the window, and click on the ‘Index Column‘ dropdown.
- To add an Index starting at 1 for the first record, select the option ‘From 1‘.
- To move the new Index column to become the first column in the data table, right-click on the column header, and select Move | To beginning. Other options are ‘Left‘, ‘Right‘, ‘To End‘.
- You can rename this query by over-writing the ‘Name’ field in the Properties section of the editor window.
- To save the query, from the ‘Home tab‘, select ‘Close & Load To…‘, and you have the choice of whether to save this query as another ‘connection only’ query, or to load the data table to a worksheet in the workbook.