98.9% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Excel Power Query
Other versions available: 2013, 2007...
Face to face / Virtual closed & onsite training. Restaurant lunch included at STL venues.
- 1 day Instructor-led
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.
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.
Syllabus
Who is this course for?
This course will benefit:
- People who work with reports, dashboards, and analysis in Excel and spending hours converting, cleaning, formatting, deleting, changing structure in source data from other Excel worksheets, financial systems, databases, websites, SharePoint, and many other sources.
- Those using Vlookup and other lookup and reference function to merge data.
- All, who want to automate the process working with external data in Excel.
- Everybody, who need to combine data to use in Excel.
- All, who get data from CSV files, text files, XML files, or other excel workbooks.
- Everyone, who would like to drop source data files into a folder and get the Excel report/dashboard updated automatically from the data in the new files, added to the folder.
- Everybody working with data models in Excel can use Power Query to extract, clean, combine, merge, and aggregate data before adding the data to a data model.
Prerequisites
An advanced knowledge of Excel.
Benefits
Learn to use Power Query to create queries, data connections to a large number of data sources and reports which will update when new data are added to the data source. Save hours of work converting, cleaning, formatting, deleting, changing structure in source data. Save hours of work updating data analysis worksheets. Save time changing source data to flat lists.Course Syllabus
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
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
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).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Virtual training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
Training formats available
|
Testimonials
Summary
Close Brothers Group plc
Ben Essel,
Senior Business Partner
Jens' enthusiasm really helps with engagement
Excel Power Query
Priory
Sundeep Kundal,
Information Analyst
Jens was a very interactive trainer. He was patient and happy to go over things when they were unclear. The topics were all very useful. Look forward to putting them into practice.
Excel Power Query
Priory
Hannah Bailey,
Operational Information Analyst
Good pace and lots of information
Excel Power Query
Get On-site/Closed quote
Excel range & versions
Excellent
HSBC
Project Manager
Alexander Orlov
I was looking for a specific training for my data analytical team to equip them with more tools to slice and dice the data, building more trust worthy sophisticated propensity models, using SQL, VBA and Excel. I came across with STL training and my research and expectations were match by 100%. Spot on! Great professionals! Highly recommended for all banking and financial professionals that are using Excel on BAU basis.
Resources
Promotions
Latest Tweet
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...
