Power Query and Power Pivot

This course is designed to teach students how to use Power Query and Power Pivot to collect, preprocess, and analyze data from multiple sources. Power Query is a data connectivity and transformation tool that enables users to discover, combine, and refine data from various sources. Power Pivot is a data modeling tool that allows users to create data models, relationships, and calculations within Excel. This course will cover key concepts and methods in data cleaning, transformation, modeling, and visualization using Power Query and Power Pivot. Additionally, students will explore the latest trends and emerging technologies in data analytics.

Course Outline:

1. Introduction to Power Query and Power Pivot

• Definition of Power Query and Power Pivot
• Importance of Power Query and Power Pivot
• Key Concepts in Power Query and Power Pivot
• Applications of Power Query and Power Pivot

1. Data Collection and Management for Power Query and Power Pivot

• Types of Data in Power Query and Power Pivot
• Data Collection Methods
• Data Preprocessing and Cleansing
• Data Storage and Retrieval

1. Power Query

• Data Cleaning and Transformation using Power Query
• Merging and Combining Data in Power Query
• Query Dependencies and Optimization
• Advanced Techniques in Power Query

1. Power Pivot

• Introduction to Power Pivot Data Models
• Building Relationships in Power Pivot
• Creating Calculated Columns and Measures in Power Pivot
• Data Analysis and Visualization using Power Pivot

1. Applications of Power Query and Power Pivot

• Financial Analysis
• Sales Analysis
• Marketing Analysis
• Supply Chain Analysis

1. Emerging Technologies in Power Query and Power Pivot

• Artificial Intelligence (AI) in Data Analytics
• Cloud-Based Data Analytics
• Predictive Analytics

Prerequisites:

• Basic understanding of Excel and data analysis
• Familiarity with database concepts

Recommended Textbook:

• “Microsoft Excel 2019: Data Analysis with Power Pivot and Power Query” by Paul McFedries.

Assessment:

• Midterm Exam (30%)
• Final Exam (40%)
• Group Project (30%)

Note: The course syllabus and assessment methods are subject to change based on instructor’s discretion