Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Power Pivot Excel 365 - From Zero to Hero

Name: Power Pivot Excel 365 - From Zero to Hero
Rating: 4.1 (65 reviews)

The only course you need to learn PowerPivot with DAX introduction

Created byPaula Guilfoyle CPA

Last updated 11/2021

English

English [Auto],

What you'll learn

How to navigate PowerPivot
How to import data to a PowerPivot data model
How to set up relationships and overcome relationship problems
How to create pivot tables and charts
How to use basic DAX calculations
How to use Time Intelligent DAX functions

Course content

5 sections • 64 lectures • 3h 10m total length

Introduction1:33
Explore power pivot in Excel 365 to analyze data, connect to data sources, build a data model, create relationships, use time intelligence functions, and build pivot tables and dashboards.
Before you forget0:12
Demonstration - Overcoming limitations of Standard Pivot tables-17:30
Demonstrates how Power Pivot removes standard pivot table limits by building a data model with linked tables, a date table, and data analysis expressions to analyze data and compute profit.
Module 1 Introduction0:50
Explore the basics of Power Pivot and navigate the user interface, learn to link tables from Excel, and start adding data to your Power Pivot model with confidence.
What is Power Pivot?1:10
How to turn on the Powerpivot ribbon1:51
Learn to enable the Power Pivot ribbon in Excel via File options and Add-ins, then open the Power Pivot window to add measures, KPI, and define relationships in data model.
How do I create a linked table from Excel to Power Pivot?1:05
Creating Linked Tables3:12
Create and rename an Excel table to import into Power Pivot, then link the table so updates in Excel refresh automatically in Power Pivot reports.
How do I find my way around Power Pivot?1:08
Power Pivot Orientation4:40
Explore the Power Pivot user interface, including the quick access bar, Excel view toggle, and the home and design ribbons for managing data models, measures, relationships, and calculations.
What other ways can I get data into Power Pivot?1:36
Connecting to other data sources4:12
Discover how to connect Power Pivot to multiple data sources—from Excel and text files to databases and online services—using get external data, preview and import, and transform data.
How to connect to a CSV or TEXT file from Powerpivot2:22
Learn to connect to a csv or text file from Power Pivot using the data ribbon's get external data, configure delimiters, headers, and column filters, and load filtered data.
How do I change data types and formatting of Power Pivot tables?0:59
How can I work with tables in power pivot?1:47
Working with Tables6:49
Verify and format data types in Power Pivot, including dates, text, numbers, currency, and formatting options, then use sorting, filters, and quick calculations for pivot tables.
Module 1 activity

Module 2 Introduction0:38
Learn to link tables with relationships in Power Pivot to perform calculations across multiple data tables and overcome many-to-many relationship problems.
What are relationships?1:31
What are fact and dimension tables?1:22
How do I create a relationship in Power Pivot?1:20
Setting up relationships in PowerPivot5:56
Set up relationships in PowerPivot by linking the credit notes facts table to the products and customers dimension tables using primary and foreign keys, via diagram view.
How do I overcome a many to many relationships?1:05
Solving the many to many relationship problem9:06
Explore solving many-to-many relationship problems in Power Pivot by building bridging tables, one-to-many relations, and a date table to align actual and budget sales in a pivot table.
How to create a unique list in Excel that can be used as a bridging table1:34
Create a unique list from a repeated customer name column using advanced filter, copy the results to a new sheet, and convert it into a bridging table for Power Pivot.
Module 2 Activity
Module 2 Activity 2
Module 2 quiz

Module 3 Introduction0:43
Explore creating pivot tables and charts from Power Pivot, learn to insert and format pivot tables and charts, and use slices and timelines to analyze data.
Refresher on Pivot Tables 14:05
Set up a pivot table in Excel from scratch to quickly analyze data like profit, cost, and sales across date, product, region, and sales rep using the pivot table wizard.
Refresher on Pivot tables 22:34
Add calculations to pivot tables using value field settings to show values as percentage of grand total, column total, row total, or running totals, accessible by right-click for quick analysis.
How do I insert a pivot table from Powerpivot?1:12
Inserting Pivot tables from Power Pivot6:39
Learn how to insert pivot tables from Power Pivot, explore standard and flattened pivot tables, use field lists, measures versus calculated columns, and build hierarchies across country, year, and month.
How do I work with a Pivot table?1:12
Working with Pivot tables from power pivot4:53
Explore pivot tables from Power Pivot data, applying filters, date hierarchy, and country fields to analyze results, with slices, relationships, and live charts.
How do I insert a pivot chart from Power Pivot?0:56
Inserting Pivot Charts from Power Pivot4:01
Insert pivot charts from Power Pivot using the Home ribbon, link charts to pivot tables, and build dashboards with independent charts using fields like customer, product, and date hierarchy.
How do I work with and format a pivot chart?1:10
Chart Elements3:31
Explore chart elements such as titles, axes, grid lines, data labels, and legends; learn to add or remove them via ribbon and crosshair, then format colors and styles for readability.
How do I insert and work with Slicers and timelines1:12
Slicers and Timelines3:51
Add slicers and timelines to pivot charts and tables with the analyze tab, link them to your visuals, and use date table to filter quarters, years, or days.

Module 4 introduction0:46
Explore DAX basics for Power Pivot, including data types, DAX indexes, the difference between calculated columns and measures, and how to use sum and sumx with related tables.
What is DAX?0:53
What data types are found in DAX and what is DAX syntax like?1:17
What are calculated Columns & Measures?1:40
Adding DAX calculations5:04
Add DAX calculations in Power Pivot using calculated columns and measures. Use the divide function to compute price per unit from total sales and quantity, then sum sales with measures.
Adding measures from Excel2:36
Learn to quickly add measures from Excel to a Power Pivot model using the new measure dialog, insert functions, test formulas, format as currency, and verify errors.
How do I use SUM and SUMX?1:13
SUM and SUMX in DAX9:59
Learn to compute total sales in Power Pivot with sum and sumx in DAX, comparing calculated columns and measures and understanding row and filter context.
How do I use COUNT and COUNTX?1:15
COUNT and COUNTX - understanding measures and columns10:59
Explore the differences between calculated columns and measures in Power Pivot 365, using count, countx, and counta to count numbers, text, and filtered rows, and when to prefer measures.
How do I work with related tables?1:29
RELATED and RELATEDTABLE6:11
Explore how related and related table connect sales and products tables, calculate cost and sales prices, and build measures with sumx and countrows using row context.
Module 4 Activity 1
Module 4 Activity 2
Module 4 quiz

Module 5 Introduction0:54
Explore time intelligence functions to compare periods and calculate running totals in Power Pivot Excel 365. Learn to use a date table for year-to-date, quarter-to-date, rolling totals, and rolling averages.
What is time Intelligent functions?1:10
What is the purpose of a date and calendar table and how do I set one up?1:16
How to set up a date table5:33
Create a day date table in Power Pivot Excel 365 with a one-click date table, auto-generating from the earliest date and including year, month, weekday fields and a date sort.
How do I use TOTALMTD, TOTALQTD and TOTALYTD Expressions ?1:14
TOTALYTD and similar functions5:19
Learn how to use Power Pivot Excel 365 time intelligence functions to calculate month-to-date, quarter-to-date, and year-to-date totals with a date table and total gross profit in a pivot table.
How can I compare to previous period?1:11
SAMEPERIODLASTYEAR3:42
Use same period last year time intelligence function to compare periods in a pivot table by adjusting the filter context with calculate to measure gross profit for the same period.
How do I calculate moving/rolling totals?0:52
DATESINPERIOD3:10
Explore creating a moving twelve month total with dates in period and date between time intelligence functions inside a calculate expression, using a date table, last date, and moving intervals.
Time Intelligence Activity0:29
Time Intelligence Solutions0:15
Time Intelligence Quiz
Adding KPI's6:11
Create and manage KPIs in Power Pivot to track sales against targets using measures, traffic-light indicators, and descriptive targets across regions and sales reps.
Course Conclusion1:25
Develop Power Pivot proficiency by navigating the interface, inserting charts and pivot tables, and building relationships for basic tax calculations. Explore Power Query for getting and transforming data.
BONUS TIME0:20

Requirements

Students should be familiar with Excel's Standard Pivot Tables
This course is recorded in Excel 365

Description

This course has been designed to take novice, Powerpivot users, to a level where they are comfortable working with Power Pivot Models, using Pivot tables and charts along with carrying out DAX calculations and using DAX time intelligence functions.

Power Pivot is an Excel add-in, available in Excel 2010 and certain versions of Excel 2013 and later. You can use Power Pivot, with its own DAX functions to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. Power Pivot is the gateway to business intelligence and data all within Excel.

This course consists of 5 modules, each with learning activities, and workbooks to download. To make sure you get the most out of this course, the learning material is made up of both videos and articles to reinforce what is covered.

Module 1

Get your head around the basics of Powerpivot, learn how to link a table, and work with that table and find out how you can get data into PowerPivot to work with it. If you are new to Power Pivot this module will show you around, so you become more familiar with the user interface and become more confident adding data to a power pivot model.

· What is Power Pivot?

· How do I create a linked table from Excel to Power Pivot?

· What other ways can I get data into Power Pivot?

· How do I find my way around Power Pivot?

· How do I work with tables in Power Pivot?

Module 2

Powerpivot allows you to perform calculations across different tables of data. In order to do this, you must link the tables together by means of relationships. Relationships can be difficult to understand at first and they are a big change to working in Excel. In this module, you will learn

· What are relationships?

· How do I create a relationship in PowerPivot?

· How do I overcome many to many relationships?

- How to set up a bridging table

Module 3

The pivot table is by far one of the most useful ways to analyze and visualize your data in Excel. Powerpivot allows you quickly insert a pivot table or chart from multiple tables to analyze or slice and dice like never before. In this module, you will learn

· How do I insert a pivot table from Power Pivot?

· How do I work with a pivot table?

· How do I insert a pivot chart from Power Pivot?

· How do I work with and format a pivot chart?

· How do I insert and work with slicers and timelines?

Module 4

Powerpivot allows basic to complex data modeling and calculations to be carried out across multiple tables of data. It does this via its powerful calculation engine using Data Analysis eXpressions. Understanding the basics of DAX is a necessity for the use of Powerpivot. In this module, you will learn

· What is DAX

· What are calculated Columns?

· What are Measures?

· What are the X Expressions?

· How do I work with related tables?

Module 5

How often do you see comparisons in accounts and reports such as Same period last year and running totals over time? DAX is equipped with a suite of time intelligence functions that are not found in excel. These functions will allow you quickly analyze your data over different time periods. By the end of this module, you will learn

· What are time intelligent functions?

· What is the purpose of a date and calendar table and how do I set one up?

· How do I use Total Month Todate and related Functions?

· How do I use Same period last year and related functions?

· How do I use Opening closing balances?

Who this course is for:

Excel users looking to upskill in PowerPivot

Power Pivot Excel 365 - From Zero to Hero

What you'll learn

Explore related topics

Course content

Power Pivot Orientation16 lectures • 51min

Relationships8 lectures • 23min

Creating PowerPivot tables and Charts13 lectures • 36min

Introduction to DAX for PowerPivot Users12 lectures • 43min

Time Intelligence Expressions15 lectures • 33min

Requirements

Description

Who this course is for: