Excel Power QueryFace to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.
From £495 List price £650
This course will benefit:
An advanced knowledge of Excel.
Getting started
Use Power Query in different versions of Excel
Navigate and get a basic understanding of Power Query
Understand Applied Steps in Query Settings
Extract Data
Extract data from excel table
Extract data from excel external workbooks
Extract data from databases
Extract data from web
Extract data from other sources
From folder (multiple files)
Transform Columns & Add Columns
Use Group By to group source data
Use first row as headers
Aggregate columns
Calculate columns
Use text functions in columns
Clean Data
Change text to columns
Remove unwanted columns and rows from data source
Cope with formatting issues
Manage are source data cleaning issues
Unpivot data
Replace errors or values in columns
Merge & Append
Merge data from an Excel workbook
Merge data from multiple Excel workbooks
Merge data from multiple data sources
Append data from Excel worksheets and multiple data sources
Automate Queries
Understand Power Query technics to automate queries
Understand how to create simple functions in Power Query
Create Dashboard/Report from Power Query data
Arguably, the most experienced and highest motivated trainers.
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
A hot lunch is provided at local restaurants near our venues:
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Available throughout the day:
Regular breaks throughout the day.
Contains unit objectives, exercises and space to write notes
Available online. 100+ pages with step-by-step instructions
Your questions answered on our support forum.
|
|
ZPG
Jess Morris,
Management Accountant - Confused.com
Really useful content, but struggled a little with the pace and of the content and conversation.
Excel Power Query
Bain & Company
Natalie Poernig,
Manager
Thank you so much - very useful session and Martin was very calm and patient! The tips and tricks thrown in there e.g. shortcuts, were so helpful too!
Excel Power Query
Central Circle Company
Molina Rebello,
Coordinator
Encourage experimentation with new tools with more examples.
Excel Power Query
| Next date | Location | Price |
|---|---|---|
| Wed 10 Dec | Online | £495 |
| Mon 22 Dec | Limehouse | £495 |
| Fri 9 Jan | Online | £495 |
| Wed 21 Jan | Bloomsbury | £495 |
| Tue 10 Feb | Online | £495 |
| Fri 20 Feb | Limehouse | £495 |
And 26 more dates...
Loading content...
Excellent
The Priory
Operational Analyst
Frances
Knowledgeable & engaging
"The trainer was very knowledgeable and engaging and the course will help me to improve my analysis."
Tutorials and discussions on MS Office
MS Office tips to save you time
MS Office shortcut keys for all versions
Handy info on industry trends
Latest news & offers
Loading content...
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
This will open the query editor where the data will be displayed.
Extract data from excel external workbooks.
Click Get Data -> From File -> From Workbook
Select the file and click Open.
Select the worksheet(s) and click Load
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.
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.
Click OK and the calculated column will now show in the query editor.
Use text functions in columns
We have text functions in Power Query. Text can be split to many columns. Text can be concatenated. We can clean text from unwanted spaced and unprintable characters and much more.
We have a text column group on the Transform tab where we have organised all the text functions. If we want to split a text column to more columns, we have several advanced options.
If we want to clean text columns, we have a format list of tools which can do most cleaning. We also have tools to extract a part of the text string if this is needed.
Replace errors or values in columns
We have a nice tool if we spot misspelled words. It could be that we spot that someone has typed the name of one of our clients wrong. We will not need to go back to the source to handle this. We can just replace it in the connection and then in all future if someone should do the same mistake Power Query will correct it.
Power Query can also be told to replace number errors.
Unpivot data
Data in Excel need to be organised in lists for several tools, and to avoid using complicated methods to get the reports and dashboards’ output. A Pivot Table can only be created from a list too. If your data is not structured as a list Power Query can structure your data correct in few seconds.
The data below is needed to be analysed in a Pivot Table.
Click inside the data and click From Table/Range.
Right click the first column header and click Unpivot Other Columns.
Change the header names and click Close & Load.
You will now have organised the data on a list on a new worksheet.
TrustScore
4.9
/ 5
2394 reviews
Call for assistance
We will call you back
Formerly Best Training
© 2025 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE
2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT, United Kingdom.
