iTrain Scotland IT Training Edinburgh image
 

Microsoft Excel Level 4- Data Analysis and Reporting with Power Query

iTrain Scotland IT Training Edinburgh image

Dates & prices
(including virtual online classroom)
Home > Courses > Microsoft courses > Microsoft Excel Level 4- Data Analysis and Reporting with Power Query

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.

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

Microsoft Copilot in Excel*

  • Overview of Copilot's capabilities within Excel.
  • Data Analysis with Copilot: Using Copilot to analyse data, identify trends, relationships, and outliers.
  • Creating Formulae: Leveraging Copilot to generate and edit formulae efficiently.
  • Editing Excel Workbooks: Techniques for modifying and enhancing Excel workbooks using Copilot.
  • Formatting Data: Utilising Copilot to format data for better readability and presentation.
  • Automating Tasks: Streamlining repetitive tasks with Copilot's automation features.
  • Visualising Data: Creating charts and graphs with Copilot to visualise data insights.
  • Best Practices: Tips and strategies for maximising the use of Copilot in Excel.

*This content is covered in full detail in our Microsoft 365 Copilot for Business Users course

 

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 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