Power Query Mastery

Module 1: Data Analysis in Excel
The module takes learners through the key data analysis tools and commands in Ms. Excel

Data skills-Intro

02:22
How To Use Filters

06:09
Using Advanced Filters

09:14
Basics of Number Formats

03:36
Advanced number formatting

01:03
How to Move/copy Sheet

01:25
How to Select and Boarder

03:05
Using a Form for Data Entry in Excel

07:27
How to format data as an Excel Table for Easier Data Management

04:60
Benefits of converting data as Excel Table

07:12
Techniques of Excel Table management

03:46
Techniques for multiple Sheet Operation

03:34
How to sum all/multiple sheets concurrently

03:25
Summing multiple sheets from Start to End

03:41
Exploring the techniques of data validation

05:24
How to remove duplicates in a list

02:53
Exploring the “Text-to-Column” Command

05:31
Further techniques: Page Lay Out – Print Titles, Print Options Gridlines, Freeze panes etc

03:05

Module 2: Graphs, Charts and Visualizations in Excel
As power query is about reporting, this module provides toolkits for report generation through visualizations, charts and graphs,

Overview of different charts in excel

09:23
Determining the best chart to fit a dataset or a required analysis

04:21
General Chart attributes and overview of pie charts

13:45
Bubble Charts

13:10
Analysis and reporting using Combo Charts

05:30
Understanding line graphs

03:38
Basics of bar and column charts in excel

05:04
Overview of box plots in excel

02:49
Box plot display options

03:02
Box Plot Axes and Data Labels

03:00
Interpreting a box plot

02:46

Module 3: Pivot Tables for Data Analysis in Excel
Master Pivot Tables. Pivot Tables are arguably the gold standard for Excel Reporting and Analytics. Pivot Tables are also a foundation for reporting the results of Power Query Data Management. This module prepares the learner to fully exploit the magic of power query by first mastering Pivot Tables.

Introduction to PivotTables using ADD DRAG AND DROP functionality

03:34
Filter for PivotTables

02:29
How to sort fields using slicers

00:58
How to sort fields based on highest values

02:54
Change analysis either as sum, count or percent depending on reporting requirements

03:29
Filter for PivotTables using SLICERS

02:23
Generate Frequency Distribution using Pivot Tables

03:44
Constructing Frequency Distribution in Pivot Table

12:39
Pivot Table reports using Smart Number Formats and Conditional Formatting

04:49

Module 4: Introducing Power Query for data cleaning, transformation and reporting automation
Gain Insight into the interface and functionality of the Power Query Editor and sample data transformations.

Module 5: Power query: Extract data from Website Upload, clean and transform data for report automation
In this module, extensive coverage is given to extracting, cleaning and transform data from an excel upload on a website. The lessons in this module give a broad first hand experience with the Power Query Editor.

Importing data into power Query, naming a query and duplicating a query

03:52
Importing data from website, naming and duplicating a query

02:27
Removing top rows, loading data to enable saving, filtering column fields, promoting headers and renaming, deleting columns

04:45
How to join two queries by appending

00:58
Generating columns using the CONDITIONAL COLUMN COMMAND

05:38
Conditional columns in power query

01:53
Data Cleaning using the FILL-DOWN command

05:07
Remove unwanted columns, change data type and move columns sideways

00:46
Split columns to generate desired data elements and further techniques for conditional columns

02:06
Further techniques in data cleaning

02:26
Change locale settings to avoid errors on converting data to ‘Date Type”

01:30
Final report (summary)

03:46

Module 6: Data Transformation Techniques
Explore endless possibilities of transforming data into an analyzable format

Module 7: Other Data merging and data cleaning techniques
You will learn how to merge files from a folder with opening the folder; How to clean, transform and arrange delimited data; How to merge data and map data points based on a Lookup-like criteria

Course Content

Module 1: Data Analysis in Excel The module takes learners through the key data analysis tools and commands in Ms. Excel

Data skills-Intro

How To Use Filters

Using Advanced Filters

Basics of Number Formats

Advanced number formatting

How to Move/copy Sheet

How to Select and Boarder

Using a Form for Data Entry in Excel

How to format data as an Excel Table for Easier Data Management

Benefits of converting data as Excel Table

Techniques of Excel Table management

Techniques for multiple Sheet Operation

How to sum all/multiple sheets concurrently

Summing multiple sheets from Start to End

Exploring the techniques of data validation

How to remove duplicates in a list

Exploring the “Text-to-Column” Command

Further techniques: Page Lay Out – Print Titles, Print Options Gridlines, Freeze panes etc

Module 2: Graphs, Charts and Visualizations in Excel As power query is about reporting, this module provides toolkits for report generation through visualizations, charts and graphs,

Overview of different charts in excel

Determining the best chart to fit a dataset or a required analysis

General Chart attributes and overview of pie charts

Bubble Charts

Analysis and reporting using Combo Charts

Understanding line graphs

Basics of bar and column charts in excel

Overview of box plots in excel

Box plot display options

Box Plot Axes and Data Labels

Interpreting a box plot

Introduction to PivotTables using ADD DRAG AND DROP functionality

Filter for PivotTables

How to sort fields using slicers

How to sort fields based on highest values

Change analysis either as sum, count or percent depending on reporting requirements

Filter for PivotTables using SLICERS

Generate Frequency Distribution using Pivot Tables

Constructing Frequency Distribution in Pivot Table

Pivot Table reports using Smart Number Formats and Conditional Formatting

Module 4: Introducing Power Query for data cleaning, transformation and reporting automation Gain Insight into the interface and functionality of the Power Query Editor and sample data transformations.

Overview of Power Query

A glimpse into sample data problems solvable and transformations in this Power Query Course

Importing data into power Query, naming a query and duplicating a query

Importing data from website, naming and duplicating a query

Removing top rows, loading data to enable saving, filtering column fields, promoting headers and renaming, deleting columns

How to join two queries by appending

Generating columns using the CONDITIONAL COLUMN COMMAND

Conditional columns in power query

Data Cleaning using the FILL-DOWN command

Remove unwanted columns, change data type and move columns sideways

Split columns to generate desired data elements and further techniques for conditional columns

Further techniques in data cleaning

Change locale settings to avoid errors on converting data to ‘Date Type”

Final report (summary)

Module 6: Data Transformation Techniques Explore endless possibilities of transforming data into an analyzable format

Data transformation from Long to Wide for easy analysis

Power Query Wide to Long, Add data-Data Refresh

Power Query Long-to-Wide data transformation of data

Module 7: Other Data merging and data cleaning techniques You will learn how to merge files from a folder with opening the folder; How to clean, transform and arrange delimited data; How to merge data and map data points based on a Lookup-like criteria

Using Power Query to Merge files from a folder without opening the folder

Using power query to undertake Data cleaning and Transformation of delimited unclean data

How to undertake merging of two queries as a trick to address lookup challenges

Module 8: Basics of Power Pivot and Data Modelling Introduction to Calculation of Measures as alternatives to columns in a data model

Using the Power Pivot Data Model to create relationships and exploring new column additions in the Data Model

Creating Measures for data analysis from a Data Model in Power Pivot

Student Ratings & Reviews

Module 1: Data Analysis in Excel
The module takes learners through the key data analysis tools and commands in Ms. Excel

Module 2: Graphs, Charts and Visualizations in Excel
As power query is about reporting, this module provides toolkits for report generation through visualizations, charts and graphs,

Module 4: Introducing Power Query for data cleaning, transformation and reporting automation
Gain Insight into the interface and functionality of the Power Query Editor and sample data transformations.

Module 6: Data Transformation Techniques
Explore endless possibilities of transforming data into an analyzable format

Module 7: Other Data merging and data cleaning techniques
You will learn how to merge files from a folder with opening the folder; How to clean, transform and arrange delimited data; How to merge data and map data points based on a Lookup-like criteria

Module 8: Basics of Power Pivot and Data Modelling
Introduction to Calculation of Measures as alternatives to columns in a data model