Power Query
What is Power Query?
Project: Use case of transforming raw sales data into a structured table.
Project: Automating monthly data imports and cleanups.
How to Import Data from Various Sources
How to Connect to a database and import data.

Data Transformation using Power Query
How to Clean and Transform Data using Power Query
Example: Removing duplicates, filtering rows, and changing data types
How to Merge and Append Queries
Example: Combining monthly sales data from multiple files.
Creating Custom Columns
Example: Adding a column to calculate sales tax.
Practical Example: Exercise on transforming a messy dataset into a clean report.
 
Pivot Tables:
Building Basic to advanced Pivot Tables
Example: Creating a pivot table to summarize sales by region.
Summarizing Data Using Different Functions
Example: Using COUNT, AVERAGE, and MAX functions in pivot tables.
Grouping and Ungrouping Data
Example: Grouping sales data by quarter
Using Calculated Fields and Items
Example: Adding a calculated field to show profit margins.
How to Create Pivot Charts
Example: Creating a pivot chart to visualize sales trends.
Customizing Pivot Chart Elements
Example: Changing chart types, adding titles, and formatting axes.
Interactive Dashboards with Slicers and Timelines
Example: Adding slicers to filter data by product category and timelines to view data over time.
Practical Examples: Exercise on creating an interactive sales dashboard.
 
Advanced Formulas
Lookup and Reference Functions
VLOOKUP and HLOOKUP
Example: Using VLOOKUP to find product prices based on product ID.
XLOOKUP: Advantages and Usage
Example: Using XLOOKUP to find employee details based on employee ID.
INDEX and MATCH: Combining for Powerful Lookups
Example: Combining INDEX and MATCH to perform two-dimensional lookups.

Date and Time Functions
DATE, DATEVALUE, and Related Functions
Example: Calculating the number of days between two dates.
Calculating Differences Between Dates
Example: Using DATEDIF to calculate age.
Working with Time and Duration
Example: Summing hours worked in a week.
Using Dynamic Arrays and the New Functions (e.g., SORT, FILTER, UNIQUE)
Example: Using UNIQUE to extract distinct values from a list.
Example: Exercise on using advanced formulas to analyze sales data.
 
Advanced Charts
Customizing Chart Elements and Styles
Example: Customizing a line chart to show monthly sales trends.
Combination Charts
Example: Creating a chart that combines a column chart and a line chart.
Using Secondary Axes
Example: Adding a secondary axis to compare sales and profit on the same chart.
How to use Data Visualization Techniques
Conditional Formatting Within Charts
Example: Adding data bars to a bar chart.
Sparklines and Mini-Charts
Example: Adding sparklines to summarize trends in a table.
Interactive Charts with Slicers and Form Controls
Example: Adding slicers to control chart data dynamically.
Practical Exercises and Examples
Example: Exercise on creating a dashboard with multiple interactive charts.
 
Enhancing Data Analysis with Slicers
Adding Slicers to Pivot Tables and Charts
Example: Adding slicers to filter sales data by region and product category.
Connecting Slicers to Multiple Pivot Tables/Charts
Example: Synchronizing slicers across different charts in a dashboard.
Advanced Conditional Formatting (1 Hour)
Custom Formatting Rules
Example: Highlighting cells based on specific criteria, such as sales targets.
Using Formulas Within Conditional Formatting
Example: Highlighting rows where sales exceeded a certain threshold.
Data Bars, Color Scales, and Icon Sets
Example: Adding data bars to show relative sales performance.
Example: Exercise on applying conditional formatting to a sales report.
Applying Learned Skills to Real-World Scenarios
Example: Analyzing a dataset of customer orders to identify trends and insights.
Common Errors and How to Resolve Them
Example: Troubleshooting common errors with VLOOKUP and pivot tables.
Tips for Optimizing Performance in Large Workbooks
Example: Using efficient formulas and managing workbook size.