Advanced Excel: Data Analysis with Power Query, PivotTables & Inquire

Module 1: Power Query – Importing and Cleaning Data

Introduction to Power Query

• Understanding the interface and main features of Power Query

• Exploring compatible data sources (CSV, Excel, Web, etc.)

• Importing data into Power Query

Importing Data

• Importing CSV files, Excel workbooks, and data from websites

• Importing an entire folder to consolidate multiple similar files into a single source

Cleaning and Transforming Data

• Removing null values, duplicates, and filtering rows

• Splitting columns, changing data types, replacing values

• Detecting and correcting errors to ensure data quality

Saving and Loading Cleaned Data

• Saving transformations and loading the cleaned data into Excel for analysis

Module 2: Inquire – Analyzing and Comparing Excel Files

Introduction to Inquire

• Overview of the tool and its main functionalities

• Accessing Inquire in the Excel ribbon and configuring comparison options

Comparing Two Workbooks

• Performing a detailed comparison of two workbooks to identify differences

• Generating comparison reports to document changes and differing values

Optimizing Excel Files

• Cleaning excessive formatting to reduce file size

• Removing unnecessary formatting to improve performance

• Managing passwords to secure files

Module 3: Power Pivot – Building Reports and Linking Data

Introduction to Power Pivot

• Understanding the interface and the benefits of Power Pivot for advanced analysis

• Enabling Power Pivot in Excel and adding data sources

Creating Data Models and Relationships

• Importing and consolidating multiple tables from different sources

• Creating and managing relationships between tables to interconnect data

Calculated Columns and Measures

• Creating calculated columns with custom formulas to enrich the dataset

• Using measures for dynamic calculations within PivotTables

Building PivotTables and PivotCharts

• Creating interactive reports with PivotTables and PivotCharts

• Using slicers for quick and targeted data analysis

Exporting and Sharing Reports

• Preparing reports for export and sharing with stakeholders

• Refreshing Power Pivot data to keep reports up to date

Other course(s) in this category

→ Advanced Excel: Data Analysis with Power Query, PivotTables & Inquire