Master the art of data manipulation and transformation using Power Query, allowing you to efficiently clean, shape, and combine data from various sources.
Excel in advanced data analysis and modeling with Power Pivot, empowering you to create dynamic relationships, calculated measures,and insightful dashboards.
Automate your data workflows saving you time and ensuring your reports are always up-to-date.
Enhance your data visualization skills by creating advanced charts and leveraging Power BI integration for dynamic, interactive reporting.
Target Audience
Analyst working in excel
Day 1
Session 1
Introduction to Power Query
Getting Started with Power Query
Introduction to Power Query.
Basic Power Query interface overview.
Connecting to Data Sources
Importing data from Excel files/ CSV files/ databases (SQL Server)/ web sources (APIs)
Data Transformation and Cleanup
Filtering and sorting data.
Removing duplicates, Splitting and merging columns.
Handling missing data
Data Source and Query Options
Managing connections and queries.
Refreshing data.
Loading data into Excel
Session 2
Advanced Power Query Techniques
Using M Formula Language
Introduction to M formula language.
Custom columns and functions.
Conditional logic in M.
Combining Queries
Merging queries, Appending queries.
Joining tables
Pivot and Unpivot Data
Understanding Pivot and Unpivot.
Using Pivot and Unpivot transformations.
Advanced pivot and unpivot scenarios.
Introduction to Power Query
Getting Started with Power Query
Introduction to Power Query.
Basic Power Query interface overview.
Day 2
Session 3
Data Modeling and Analysis
Data Modeling with Power Pivot
Introduction to Power Pivot.
Creating relationships between tables.
DAX functions for data modeling.
Advanced Data Analysis
Creating calculated columns and measures.
Slicers and timelines.
Building interactive dashboards.
Data Cleaning
Session 4
Data Visualization and Reporting
Advanced Charting and Graphs
Creating advanced Excel charts.
Session 5
Data Security
Data Security and Sharing
Protecting sensitive data in Excel.
Sharing workbooks securely.
Trainer Profile
Trainer has a rich experience in data science and financial modeling areas. He is a versatile trainer equipped to handle topics like machine learning, programming languages in Python and R and market risk analysis. He is a engineer and MBA by education.