IT Computer Training Courses Edinburgh, Glasgow, Scotland: Microsoft Excel Level 4- Data Analysis and Reporting with Power Query Computer Training: iTrain Scotland: Microsoft Office 2016 Training, Adobe Training, Windows 10 training, Office 2013 training, Computer Courses, Computer training room equipment hire

Classroom with tutor
or
Live virtual classroom

1 Day

Training Options:

Public Schedule

Onsite anywhere in UK

Company course

Tailored content

Classroom with tutor
or
Live virtual classroom

1 Day

Public Schedule

Onsite anywhere in UK

Company course

Tailored content

Public Schedule

Onsite anywhere in UK

Company course

Tailored content

Excel Level 4- Data Analysis and Reporting with Power Query

Suitable for versions 365 / 2019 / 2016

This course introduces Microsoft Excel's powerful data modelling and business intelligence tools- Power Queries and Power Pivot Tables. Import data from almost anywhere (inc. databases, SharePoint, Oracle, SQL, websites, cloud, and many more) and use powerful tools to transform the data (shape, merge, clean, append). Create powerful reports using Pivot tables and Pivot Charts based on data models merging and live linking many sources of data.

Our POWER BI DESKTOP course is strongly related to this course.

Course content

Prerequisites

This course can be undertaken by non-technical existing Microsoft Excel end-users. It is recommended, although not essential, that delegates have a basic understanding of the concepts in our Excel L1 course.

Course goals

Understand relational databases and multiple data sources
Use Excel’s Power data tools to manipulate data
Create powerful and professional reports

Topics

Working with data in Excel

Database rules
Data tables
Dynamic named ranges
Slicers

Pivot Tables

From data table
From multiple tables / sources
Integration with Power Query
Pivot Charts
Reporting tools
Loading multiple tables
Data model

Introduction to Power Query

Query concepts
Get & Transform data
Queries & Connections
Data tools
Launching the Power Query Editor

Introduction to Relational databases

Creating reports from multiple tables / data sources
Primary & foreign keys
Types of relationship- one-many, many-many
Types of join- Inner, Outer

Importing data from

Separate tabs (sheets) of the same Excel file
External Excel workbooks
SharePoint / web / cloud
Oracle
SQL
CSV
Microsoft Access
All other sources

Shaping data

Filter / sort / group
Formatting / converting data type
Show / hiding columns
Replacing text / values / missing & Null values
Transpose

Transforming data

Splitting / merging columns
Concatenation
Extracting data
Delimiting data
Trimming and cleaning
Adding prefix / suffix
Creating calculated fields
Un-pivoting
Filter for errors
Creating custom columns

Creating & Storing Queries

Simple queries
Advanced combined queries
Merge multiple tables
Append from multiple sources
Understanding query / applied steps
Table links and relationships
Adding a query to a data model
Loading a query to Excel worksheet
Refreshing / updating data
Live link to cloud

Excel Functions Vs Power Query

Merging / Combining Vs VLOOKUP, MATCH & INDEX
Transforming data Vs Text functions
Pivot Tables Vs SUMIF, COUNTIF

Prices

Prices below are for scheduled courses held in our Edinburgh Training Centre or Live Virtual Classroom. Major discounts available for onsite or private courses: click General Enquiry button below to enquire about private courses.

Group discount

1 place £ 350 n/a

2 places £ 700 n/a

3 places £ 1050 £ 956

4 places £ 1400 £ 1260

5 places £ 1750 £ 1558

6 places £ 2100 £ 1848

7 places £ 2450 £ 2132

8 places £ 2800 £ 2408

		Group discount
1 place	£ 350	n/a
2 places	£ 700	n/a
3 places	£ 1050	£ 956
4 places	£ 1400	£ 1260
5 places	£ 1750	£ 1558
6 places	£ 2100	£ 1848
7 places	£ 2450	£ 2132
8 places	£ 2800	£ 2408