Dealing with data and building analysis efficiently and effectively have become core skills for today’s accounting and finance professional. Power Pivot, part of Microsoft Excel and Power BI suite, is indispensable for transforming, loading and analyzing data (the “TLR” of the commonly referred to ETLR – “Extract, Transform, Load, Report”). This session builds on the “Introduction to Power Query” session and explores working with the Data Model, building relationships between queries, creating a date table, building basic “DAX” functions and building pivot tables with the new supercharged functionality that Power Pivot allows.
Course ID: IDAEPP
Introduction to Data Analytics with Power Pivot
Learning Objectives
- Understand loading queries into the Data Model and the advantages of this capability
- Navigate the creation of a basic date table via Power Pivot and building a relationship to a data query
- Understand building relationships among queries and creating pivot tables from multiple connected data tables
- Understand the differences, and advantages, between pivot tables and Power Pivot pivot tables
- Build, and leverage, basic data analysis expressions (“DAX”) functions
Major Topics
- In this course, we build upon the intro to Power Query session and dive into Excel’s “pivot tables on steroids” tool – Power Pivot. We will explore the basics of transforming data, the advantages of loading data into the Data Model, building relationships between queries, and building robust analysis with the more powerful Power Pivot pivot tables. The “analysis” phase of data analysis really starts to take shape in this session. Topics include:
- Loading queries into the Data Model (in the “background” of Excel)
- Building a date table – why they are important and the advantages of using a well-structured date table
- Building pivot tables from multiple, related data sets – taken advantage of the “power” of power pivot
- Creating and using DAX functions to simplify and automate calculations required for analysis purposes
- Hands-on, interactive, session components to apply the knowledge you learn – using real data relevant to accounting and finance professionals
- Excel workbooks will be made available for all participants to use at their leisure and further explore the topics discussed
Who Should Attend
Any business professional responsible for, or interested in, managing and working with data and delivering insight to their organization
Fields of Study
Computer Software & ApplicationsPrerequisites
Comfortable with Excel navigation; Understanding of data related topics – LOOKUP, Pivot Tables, Data Tables, etc. would be helpful, but not required