COURSE DESCRIPTION

In today's data-driven landscape, Excel Power Query and Power Pivot have emerged as indispensable tools for data analysts. Power Query facilitates seamless data integration by enabling analysts to effortlessly connect, transform, and clean data from a variety of sources. Its user-friendly interface empowers analysts to reshape and merge data with ease, reducing the time spent on manual data preparation tasks. Complimenting Power Query, Power Pivot furnishes a robust data modeling environment within Excel, facilitating the creation of sophisticated relationships and calculations across vast datasets. By harnessing the in-memory processing capabilities of Power Pivot, analysts can swiftly construct intricate data models, perform advanced calculations, and generate insightful visualizations that unveil hidden patterns and trends. This combination of Power Query and Power Pivot enhances analytical agility, enabling data analysts to work with larger datasets, uncover meaningful insights, and produce accurate reports and visualizations. By streamlining the data preparation and modeling processes, these tools enable analysts to focus more on data interpretation and strategic decision-making, ultimately maximizing their productivity and the value extracted from the data.

COURSE CONTENT

1 – Date, Time, and Text Functions

• Work with Date and Time Functions
• Work with Text Functions

2 – Data Validation and Lookup Functions

• Implement Data Validation, Forms, and Controls
• Use Lookup Functions

3 – Presenting Visual Insights with Dashboards in Excel

• Visualize Data with PivotCharts
• Filter Data Using Slicers and Timelines
• Create a Dashboard in Excel

4 – Power Query

• Connect to Data with Queries
• Clean and Combine Data
• Shape and Transform Data

5 – Getting Started with Power Pivot

• Enable and Navigate Power Pivot
• Manage Data Relationships

6 – Visualizing Power Pivot Data

• Create a Power Pivot Report
• Create Calculations in Power Pivot

7 – Working with Advanced Functionality in Power Pivot

• Create a Key Performance Indicator
• Work with Dates and Time in Power Pivot

8 – Alternate Lesson: Power Pivot

• Create Data Models with Power Pivot
• Create Power Pivots
• Perform Advanced Data Analysis and Visualization

 Appendix: Commonly Used DAX Functions

This course is designed for advanced users of Microsoft Excel who have experience with Excel and can create and analyze basic PivotTables and PivotCharts. You can obtain this level of skills and knowledge by taking the following MicroAge class:
• Excel Level 3: Data List Management

Additional workplace experience with Excel is highly recommended.