Instructor-led training -

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Excel Power QueryExcel Power Query

Other versions available: 2013, 2007...

Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.

Designed for Excel 365

From £495 List price £650

Power Query will change the way you work with data in Excel and will save you hours of time that you can then use to explore your data, progress powerful understandings, and make better decisions. Power Query has endless ways of handling data sets.

With Power Query's data connection technology you can connect, combine, merge and refine data sources to meet your analysis requirements from a large number of data sources.

Using Power Query you can create a query that imports data from a web page and if the source data change the query will refresh the data in your Excel workbook.
Power Query lets you share and manage queries as well as search data within your business. The queries can be shared and used by others in your organisation.

Training manual sample

Below are some extracts from our Excel Power Query manual.

Unit 3: Extract Data  

In this unit you will learn how to: 

  • Extract data from excel table  

  • Extract data from excel external workbooks 

  • Extract data from databases 

Extract data from excel table  

To extract data from Excel the data most be stored in an Excel Table.  

What is a Table? 

In Excel, a table is a specially designated range of numbers. This special range of numbers has added functionality that other cell ranges do not have. You can have more than one table in a workbook or worksheet if you want, and tables can be as large or small as the amount of data you want to work with. 

Normally a table is made from adjacent columns of data, with a unique label or heading for each column. Each row in the table should have entries organised according to the column headings. You should keep your table data adjacent in a block to take advantage of all of Excel’s table features. Some Excel features, like filters and PivotTables, will not work correctly if the data is not blocked together in adjacent columns as a table. 

One of the key advantages with working with a Table is that the table range will automatically increase as more rows are added. If you have used an Excel Table as the basis for a PivotTable, when the PivotTable is refreshed it will grow to accommodate these new rows. These new rows can either be typed manually or pasted on to the bottom of the table. 

 

Creating Tables 

To create a table from an existing range: 

  • Pre-select a range of data in adjacent columns or click any cell within the required data range. 

  • On the Home Ribbon, Style group, click on the Format as Table button. 

  • This will display a menu of table formatting options. If you click on one of the table menu options, the selected range will be formatted as a table based on the style of your choice. 

  • When you choose a table format, you will see a Format as Table dialogue box appear. 

 

  • If there are column headings in the first row of the range you selected for your table, check the box that says, “My table has headers.” 

  • Make sure the cell range shown is the range that you want for your table;  

  • Click the OK button to create your table. 

 

 

To extract data from Excel table, click inside the table and click From Table/Range 

Table

Description automatically generated 

This will open the query editor where the data will be displayed. 

Graphical user interface, application, table

Description automatically generated 

 

Extract data from excel external workbooks. 

Click Get Data -> From File -> From Workbook 

Graphical user interface, application

Description automatically generated 

 

 

Select the file and click Open. 

Select the worksheet(s) and click Load 

Graphical user interface, table

Description automatically generated 

 

         

Extract data from databases 

Source data stored in database can easily be extracted by Power Query. Click From Database and selected the database. If your database is not listed, you can click From Other Sources and ODBC (Open DataBase Connectivity) and connect to the database.                    

Select the tables or queries in the database you want to extract to Excel. The data can now be loaded to the destination workbook, or loaded as connection only, or if you want to edit the query, you can click Transform. 

                

Extract data from web 

Click From Web, enter the URL and click OK. The Navigator will display the data and again the data can now be loaded to the destination workbook, or loaded as connection only, or if you want to edit the query, you can click Transform. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Unit 4: Transform Columns & Add Columns  

In this unit you will learn how to: 

  • Use Group By to group source data 

  • Calculate columns 

  • Use text functions in columns 

  • Replace errors or values in columns 

  • Unpivot data 

Use Group By to group source data 

The Group By option can be used to get subtotals from source data and can be very useful if you have many to many relationships between multiple source tables. Any number of columns can be grouped, and any number of aggregations can be applied to the query. 

 

 

 

Calculate columns 

We can make any number of calculated columns in Power Query. This can be useful to automate all steps. In this example we have quarterly profit from clients and want a calculated column to display annually income. 

Sheet2 - Power Query Editor 

Click Custom Column on the Add Column tab. Give the column a name and type the calculation in the Custom column formula box. On the Available columns list all for the calculated column available columns are listed. Just double click on the column name in the Available columns list when you need to reference the column in the calculation. Here it is just a simple sum.