Overview

Unlock the full potential of Excel for advanced data analysis with this four-week course. Designed for aspiring data analysts and business professionals, you'll master Microsoft's powerful tools: Power Query, Power Pivot, and Power BI. This program will transform your ability to handle, analyze, and visualize large datasets, moving beyond traditional spreadsheet limitations. By the end of this course, you will be able to: * Automate data import and transformation using Power Query. * Perform advanced data modeling and analytics with Power Pivot and DAX. * Create dynamic, interactive reports and dashboards in Power BI. * Efficiently manage and analyze millions of rows of data within Excel. To succeed, we recommend prior experience with Excel fundamentals and data visualization, ideally from our 'Data Analytics and Visualization' Specialization. This course is designed for Excel 2016 and later, with Office 365 recommended for optimal experience. Please note: Power Query, Power Pivot, and Power BI Desktop are Windows-only.

Syllabus

Course Introduction and Setup

Welcome to your journey into Excel Power Tools for Data Analysis! In this introductory module, you'll get a comprehensive overview of how Power Query, Power Pivot, and Power BI will revolutionize your data handling. You will learn to import and transform data efficiently, work with massive datasets, and create compelling interactive reports. This material will orient you to the course's structure and goals. We encourage you to reflect on your learning objectives and share them with your peers to maximize your experience.

Get and Transform (Power Query)

Ready to begin your data analysis journey? This module focuses on the crucial first steps: importing and combining data. You'll discover how to leverage Excel's powerful Get and Transform feature, also known as Power Query, to efficiently bring data from various sources into your workbook. Learn the different methods for combining datasets to meet your specific data analysis needs. To get the most out of this module, try importing data from a new source each week.

Data Transformation with Power Query Editor

Welcome to the exciting world of data transformation! Building on your data import skills, this module dives into how you can reshape your data for analysis. You'll learn to pivot data between wide and long formats, group information, and split columns using Power Query's powerful features. These techniques are crucial for preparing your datasets for accurate and insightful analysis. To succeed, practice each transformation step as you learn.

Power Pivot and Data Modeling

Ever hit Excel's 1 million row limit? This module introduces the powerful Data Model in Excel, allowing you to analyze millions of rows of data, limited only by your computer's memory. You'll learn to define database-like relationships between tables and visualize your data using Power Pivot and advanced cube functions. Master creating dynamic PivotTables from these robust data models. For best results, practice building complex data relationships.

Data Visualization with Power BI

Get ready to elevate your data presentation! In this module, we transition from Excel to Power BI, Microsoft's leading Business Intelligence tool. You'll apply your foundational skills from Power Query and DAX to design and build dynamic, interactive reports and dashboards. Learn to transform raw data into compelling visual stories and share your insights effectively. To master Power BI, experiment with different visualization types for your data.