Course Description
This 3-day course covers the importing, cleaning, manipulating and analysis of data sets and databases within the overall Excel environment, including PowerQuery and Power Pivot.
The first day of the course covers the use of a wide set of traditional native Excel functionality and advanced functions to manipulate and analyse data. These include the use of functions form many function categories (Lookup, Text, Date, Information, Logical) as well as Database functions, filters and advanced filters, PivotTables, and general aggregation and conditional functions.
The second day of the course is devoted to the use of PowerQuery to manipulate, integrate, merge and consolidate data, and to create grouped analysis and other queries. We show the applicability of this tool to many areas, ranging from simple yet powerful analysis, to more advanced features that can allow for new forms of analysis or save significant time. Hands-on exercises include transposition, pivoting and unpivoting data, the creation of grouped-based reports, and the appending or consolidation of data sets, and introduction to the use of the M-language.
The third day covers Power Pivot. The focus is on the creation relational databases with an Excel Data Model, and the creation and interpretation of DAX measures to create powerful queries of the data. We recap the core feature of Pivot Tables, such as filters and slicers, and their effect and interpretation with a filter context. We cover the use of CUBE functions. We briefly mention Power BI for data visualisation and the links to PowerPivot and the other data analysis approaches covered in the course.
The many hands-on exercises used throughout the course aim not only to develop a strong capability across a wide set of application areas, but also a good understanding of the advantages and limitations of the various analytic options available. This ensures that one makes the most appropriate choice of approach when faced with a practical situation.
Learning Objectives
- Learn the full range of approaches to analyse data within an Excel environment
- Understand the advantages and limitations of each, and enhance one’s ability to choose the most effective and appropriate method
- Learn how to clean and manipulate data sets using Excel functionality and advanced functions
- Understand areas or activities where automation or further tools would be most effective
- Develop practical skills in cleaning, manipulating, integrating and analysing data sets in Excel
- Learn how to use PowerQuery to import, clean manipulate and analyse data
- Understand when to use intermediate or connection only queries
- Develop skills in using PowerQuery for more advanced applications
- Understand benefits of limitation of PowerQuery compared to native Excel
- Learn the use of PowerPivot to analyse data sets with PivotTables, filters, and slicers
- Understand the meaning and interpretation of DAX measures within a filter context
- Gain practical skills in creating reports using measures and calculated columns
- Learn how to create and analyse relational databases using the DataModel
- Gain an overview of advanced features of PowerPivot
Key Topics Overview
- Filters and searching · Sorting · Advanced filters and data extraction · Dynamic sorting
- Aggregation functions · Conditional functions · Database functions
- Lookup, Text, Date, Information and Logical functions
- Removing duplicates and errors · Splitting and combining text fields
- Unique keys · Creating flat tables · Multiple data sets
- Calculated columns for advanced queries
- Power Query · Linking to external data sources · Cleaning data
- Manipulating data · Transposition · Pivoting · Unpivoting · Grouped queries · Appending and consolidation of data sets
- Using the M-language · Variable types · Date formats
- PivotTables · Filters · Slicers
- PowerPivot · Excel Data Model · Relational databases
- DAX language and measures · Understanding and interpretation evaluation contexts
- Functions and iterator functions
- Sensitivity analysis · CUBE functions · Time-intelligence · Complex queries
Course Dates
Course Agenda in Detail
Day 1: Analysis of Data Sets in Excel
AM: Core Tools and Functions for Excel Data Analysis
- Course overview and introduction
- Overview and interpretation of key measures of single and multi-variate data, and statistical analysis
- Core approaches to analysing a dataset, with hands-on exercises:
- Use of filters, data sorting and searching
- Creating category lists, removing duplicates and combined duplicates, basic data cleaning operations
- Use of aggregation functions, conditional functions and their variants (structurally fixed queries)
- Using PivotTables and database functions (structurally variable queries)
- Using filters, advanced filters and extraction
- Creating calculated columns for analysis by dates, more complex queries, and other criteria not explicit within a data set
- Confidence intervals, and hypothesis testing
PM: Cleaning, Manipulating and Integrating Data Sets with Excel
- Overview of functionality and functions (native Excel) to clean and manipulate data
- Toolbar functionality and ad hoc methods
- Overview of using functions to manipulate data
- Hands-on exercises
- Data cleaning, splitting, manipulation, correction, and recombining of data sets; using functionality and functions
- Linking multiple data sets by creating unique keys and with lookup processes
- Overview and demo of using macros to automate repeated manual or ad hoc approaches
- Day 1 Recap, Q&A, Discussion, Close
Day 2: Data Preparation, Analysis, and Reporting using PowerQuery
AM: Introduction and Foundation Functionality
- Introduction; recap and follow-up of data manipulation approaches in native Excel
- Overview of PowerQuery: key menu toolbars and icons; types of queries, reports and their placement
- Hands-on exercises
- Data cleaning, manipulation (e.g. splitting lists, recombining fields)
- Pivoting and unpivoting
- Reporting as full lists and grouped queries
- Use of calculated columns; comparison with Excel-formulae
- Creation of Excel reports and connection-only queries
PM: Further Applications and Introduction to PowerPivot
- Hands-on exercises
- Appending data sets (e.g. for on-going flexible reporting, consolidation of data from business units, geographies or time-periods, …)
- Use of data sources outside the current workbook (e.g. csv files, websites, combining data from subfolders)
- Further work with M-language and advanced calculations
- Comparison of native Excel versus PowerQuery; benefits and most appropriate applicability of each
- Introduction to PowerPivot
- Overview of the Excel Data Model and relational databases
- Hands-on exercises
- Creation and format of PivotTables using standard (implicit) measures
- Use of filters, slicers, and drill-downs
- Day 2 Recap, Q&A, Discussion, Close
Day 3: Using PowerPivot for Data Analysis; Introduction to Power BI Desktop
AM: Introduction to PowerPivot and Its Key Features
- Introduction and recap of PivotTables
- Introduction to explicit DAX measures and calculations; use of calculated columns
- Hands-on exercises
- Calculated columns
- Writing single-column measure formula, based on single tables of data (incl. benefits compared to native Excel and PowerQuery)
- Using inputs from multiple columns of the same table; iterator functions
- Measures requiring filters and conditional calculations
- Linking tables with relationships
- Measures that vary with selected slicer categories, floating tables and sensitivity analysis
- Hands-on exercises:
- Measures requiring data from multiple related tables
- Further tips to get the most from core PivotTable analysis
PM: More Advanced Reports; Introduction to Desktop Power BI and Visualisation
- Hands-on exercises:
- Use of CUBE functions
- Creation of date tables and use in time-intelligence functions
- Introduction to other possibilities and advanced measures
- Introduction to Power BI and Visualisation
- Hands-on exercises:
- Creation of reports and graphics with Power BI
- Day 3 Recap, Q&A, Discussion, Course Close