TARGET AUDIENCE
Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyse and display data.
TRAINING OBJECTIVES
At the end of this course, delegates will be able to:
Create effective and professional reports
Gather and transform data from multiple sources
Discover and combine data in mashups
Learn about data model creation
Explore, analyse, and visualize data
COURSE CONTENTS
Introduction to Power Query
Installing Power Query
Create and Edit a simple query
Common Data Import Sources
Working with CSV; Text; Excel Files
Importing multiple files
Working with Web data
Scraping Data from Web Pages
Calling a Web Service
Finding Data using a Power Query Online Search
COURSE OUTLINE ::::
Working with Folders and Multiple Files
Using data from Windows File manager
Combining Data from Multiple Files
Working with Columns
Name; Move; Split; Merge
Filtering and Sorting
Using Auto-Filter
Using Number, Text and Date Filters
Filtering Rows by Range
Removing Duplicate Values
Filtering out Rows with Errors
Sorting
Grouping rows
Changing Values in a Table
Replacing Values
Transformations: – Text; Number; Date/Time
Replacing Missing Values
Table Transformations
Un-pivoting Columns to Rows
Transposing a Table
Creating Custom Columns
Loading Data
Loading Data into a Worksheet
Loading Data into the Excel Data Model
Power Query and Table Relationships
Refreshing Queries Manual & Auto
Query Editor
Edit Query Steps
Edit Query Step Settings
Refresh a Query
COURSE OUTLINE ::::
Queries
Understanding Power Query’s language syntax
Merging tables and queries
Using Power Query functions in columns
Using conditional (IF) statements
Creating custom Power Query functions
Implementing dynamic parameter tables
Creating calendar tables
Sharing queries
Best practices for Query organization
Programme scope, delivery arrangements and suitability are confirmed during enquiry.