iTrain Scotland IT Training Edinburgh image
 

Excel Data Analysis and Reporting with Power Query and Pivots

iTrain Scotland IT Training Edinburgh image

Home > Courses > Microsoft courses > Excel Data Analysis and Reporting with Power Query and Pivots

Excel Data Analysis and Reporting with Power Query and Pivots

Suitable for versions 2016 / 2013 / 2010

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.

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.
  • Understand relational databases and multiple data sources
  • Use Excel’s Power data tools to manipulate data
  • Create powerful and professional reports

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 below are for scheduled courses held in our Edinburgh Training Centre. Major discounts available for onsite or private courses: click General Enquiry button below to enquire about private courses.
Group discount
1 place £ 305 n/a
2 places £ 610 n/a
3 places £ 915 £ 865
4 places £ 1220 £ 965
5 places £ 1525 £ 1065
6 places £ 1830 £ 1165
7 places £ 2135 £ 1265
8 places £ 2440 £ 1365