Power Pivot Workshop Complete Bundle

Learn to master Power Pivot for Excel and DAX. This bundle includes three courses: Beginner, Intermediate, and Advanced.
4.7 (82 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
876 students enrolled
$120
Take This Course
  • Lectures 75
  • Length 10 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2014 English

Course Description

This bundle include all the three courses of Power Pivot Workshop: Beginner, Intermediate, and Advanced. The Beginner course introduces Power Pivot to any user who knows Excel and want to create reports with more complex and large data structures than a single table made by few thousand rows. The Intermediate course improves your skills on Power Pivot for Excel, introducing you to the DAX language and important features such as CALCULATE and Time Intelligence functions. The Advanced course enables you to master Power Pivot for Excel. You will understand DAX in depth, which is required for writing complex calculations. You will also learn how to create complex reports and data models by leveraging advanced features of both Excel and Power Pivot.

Power Pivot is available in both Excel 2010 and Excel 2013, and it is the center of Power BI, the Microsoft offer for self-service analytics. You do not need any previous experience in Power Pivot to take this course.

The course includes almost 10 hours of video in 73 lectures, divided in these sections:

  • Introduction to Self-Service Business Intelligence
  • Introduction to Power Pivot
  • Loading Data
  • Data Modeling
  • DAX Language
  • Table Functions and CALCULATE
  • Basic Time Intelligence
  • Power BI and SharePoint
  • Creating Reports and KPI
  • Advanced DAX Concepts
  • Advanced DAX Functions
  • DAX as a Query Language
  • DAX Patterns

What are the requirements?

  • Power Pivot for Excel 2010 or Excel 2013

What am I going to get from this course?

  • By the end of the course, you will master Power Pivot for Excel.
  • You will learn how to create simple models in Power Pivot, loading data from different data sources.
  • You will learn how to write DAX expressions performing calculations such as Ratio-to-Parent, Year-to-Date, Year-Over-Year and so on.
  • You will learn how to use advanced DAX functions and to create complex data models in Power Pivot.
  • You will learn how to solve typical business issues by applying predefined patterns in DAX.

What is the target audience?

  • Advanced Excel Users
  • Business Analysts
  • Business Intelligence Developers
  • Data Scientists

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction to Self-Service Business Intelligence
03:51

In this module you learn the basics of Self Service BI, the new trend in building reports with Microsoft Excel. You learn the basic terminology of data warehouse, data mart and the right place of Power Pivot in the flow of data, from raw tables to exciting reports. This background theory will greatly help you learning better the next lectures.

02:09

What is Power Pivot and why millions of people are learning it? This module shows a demo of how to load data from a database and build a simple sales report.

06:13

Power Query is the new exciting tool to load data from different databases. In this demo, you see how to merge the data loaded in the previous module with demographic information that you can easily find on the web. You do not need SQL skills: just point and click to load and shape the data the way you need it.

04:42

Once data coming from your database is in Power Pivot, you can build interactive reports using Power View: the analytical tool included in Excel 2013, which makes it easy creating interactive reports.

03:55

Power Map allows you to project data on maps showing distribution of numbers over the territory, and to create animated stories about your data.

Section 2: Introduction to Power Pivot
07:53

PivotTables in Excel are the main tool to navigate in data models created with Power Pivot. This module describes the features available in both Excel 2010 and Excel 2013. The goal is to create a solid background on what is a PivotTable, how it works and what are its limitations. In the following lectures, you learn how to overcome those limitations.

05:07

Excel 2013 greatly enhanced PivotTables with the introduction of the Data Model and a deeper integration with Power Pivot. In this lecture, you learn what the data model is, how to activate it, and how to start using Power Pivot.

09:08

In this lecture, you learn how to load data in the Power Pivot window, how to create basic relationships, and how to use the Power Pivot user interface to complete basic operations.

10:49

Calculated columns are a simple and very effective way of enriching the expressiveness of your model, by adding basic row-by-row calculations to your tables. In this lecture, you learn the basics of calculated columns: how to create and use them.

05:02

You can turn any Excel table into a Data Model table by using Linked Tables, which are a great way to enrich your model with data you prepare in Excel.

05:08

Calculated fields add advanced calculations to the data model. They compute values over totals instead of computing values row-by-row, making it possible to compute percentages, distinct counts and other non-trivial calculations.

03:46

Once you complete a report, you might want to share it with other people. Power BI transforms any Excel workbook containing Power Pivot data into an interactive web-based report, so that authorized people using a web browser can see and navigate it.

Section 3: Loading Data
04:56

This lecture provides you a deeper analysis of the difference between loading data from Excel tables and loading data from data sources. You learn why it is important loading data directly into the model and what are the basic tools to use.

06:52

A relational database accessible by using SQL queries is probably the most common data source. Here you learn the basic techniques to load data from SQL Server tables, how to connect to a relational database and load the content from available tables and views, without writing SQL queries.

08:33

Even if you are not familiar with the SQL language, you can use the SQL Wizard to create custom queries that populate your data model. You just have to be aware of a few details about relationships, which you learn in this lecture.

04:13

This lecture shows a few advices about importing data from Microsoft Access.

07:47

If you have data stored in Analysis Services databases (also known as “Cubes”), you can import that data in Power Pivot, too. This lecture shows how to overcome a few limitations existing in importing data from SQL Server Analysis Services.

04:59

You have already seen Linked Tables in a previous lecture. Now you can see more details about how they work and how to get the most out of this feature.

03:37

If you have data stored in other Excel workbooks, you can import them in a Power Pivot data model. This lecture explains how to do that and how to avoid the most common mistakes.

01:23

This lecture described the Text File Data Source, which imports data stored in text and CSV files.

04:01

This lecture shows how to load data from any program by using the copy and paste feature, using the Windows clipboard.

04:23

You can populate a data model by loading data from Reporting Services reports in a fast and efficient way, without having to worry about the removal of formatting and graphics.

02:01

You can load data published on certain web sites (e.g. SharePoint) in Power Pivot using the data feeds. This module shows you how to import OData data feeds supported by Power Pivot.

03:55

You can load data from the Azure Marketplace, where you can find data collected and sold (or provided for free) by other companies.

02:19

In this lecture, you learn the techniques to load data from SharePoint lists, reports stored in SharePoint, and Power Pivot workbooks already published in SharePoint.

04:39

In this lecture, you learn how to modify the data connection automatically created by Power Pivot in order to enable automatic refresh on open, to activate periodic data updates, and to disable the automatic refresh on connection that you want to update only manually.

Section 4: Data Modeling
12:06

Working with Power Pivot you will become a data modeler. This lecture provides the basic information about what is a data model and why modeling is important in Power Pivot.

09:08

What does it mean to normalize or de-normalize a model? Knowing when to denormalize is a very important skill, which is useful creating data models for Power Pivot.

07:11

In modern data warehouses, the shape of data is usually a star schema. In this lecture, we introduce the concept of star schemas and explain why it is important to shape data in this way, maximizing the usability of the data model.

10:25

You learn that denormalization is important, but what happens if you denormalize too much? At that point, the data model is no longer easy to use. This module introduces the concept of over denormalization and simple techniques to solve the issue, if present.

Section 5: DAX Language
13:37

DAX is the programming language of Power Pivot. This module provides the basic information to understand what a DAX expression is and how to start writing your DAX code.

19:01

It is important to learn when to use calculated columns versus calculated fields. They are useful in different scenario. In this lecture, we highlight the differences between them, and clarify when to choose one or the other.

04:17

Your formulas can contain errors, due to bad or unexpected data. In this lecture, you learn how to prevent errors in data that might break your reports.

18:50

This lecture shows some examples of most of the categories of DAX functions, which we will use extensively in the following lectures.

Section 6: Table Functions and CALCULATE
10:55

In this lecture, you learn the RELATED and the RELATEDTABLE functions. Both are useful to navigate through relationships and evaluate expression over them. We also introduce the concept of table functions, i.e. functions that return tables.

12:40

FILTER and ALL are the most commonly used table functions, and you will learn when and how to use them.

11:12

CALCULATE is a powerful function, useful in many calculations. This lecture explains the basic functionality of CALCULATE.

03:58

Do you want to compute the percentage of a cell in a PivotTable against the visible total, in case a slicer filters data? Then ALLSELECTED is the function for you.

Section 7: Basic Time Intelligence
04:51

Year-To-Date, Month-To-Date, Comparison of sales in the current period compared with the same period in the previous year. All this is what we refer to as “time intelligence”, which is the topic introduced in this lecture.

16:14

Before using Time Intelligence functions, you have to build a good calendar table. This lecture shows how to do that, avoiding common mistakes.

05:49

Calculation of working days difference between two dates, calculation of sales per working days: these are just two examples of the kind of calculations explained in this lecture.

05:10

If your data models includes more date, such as ship date and order date, you have to adapt the data model, for example by creating more calendar tables. This lecture shows how to do that in the right way.

11:58

This lecture provides an in-depth explanation of Year-To-Date, Month-To-Date, comparison of sales with the same period in the previous year, and other similar Time Intelligence calculations.

Section 8: Power BI and SharePoint
02:54

This lecture introduces a deeper explanation of the features available in Power BI and SharePoint to publish Power Pivot workbooks.

14:52

Power Query will quickly become your best friend whenever it is time to load data. It is extremely powerful and easy to use as a data management tool inside Excel.

10:09

It is impossible to describe the experience of using Power View. You need to try it by yourself. This lecture shows how to get the best out of Power View and how to shape your data in the correct way, improving the user experience navigating data.

04:49

Power Map is a gorgeous tool to create stories about data showing them on maps. You can create great presentations by coloring areas based on data values and by building entire movies about your data.

07:17

This lecture explains how to publish your data to the Office 365 platform, sharing reports with colleagues and stakeholders using a simple internet browser.

04:35

This lecture shows how to publish your data to SharePoint. This is useful if your company adopted SharePoint as a collaboration tool and installed Power Pivot for SharePoint.

Section 9: Creating Reports and KPI
12:00

KPI are a powerful feature of Power Pivot, making the interpretation of data quicker and easier. This module introduces the KPI feature, explaining in detail how and when to use it.

20:49

Excel and Power Pivot have some hidden gems that might prove extremely useful in specific scenario. This lecture describes how to use sets and custom aggregations, simplifying the creation of complex reports.

13:29

If you plan to use Power View to build interactive reports, then you need to learn how to improve your data model design for Power View, which is what you will learn in this lecture.

Section 10: Advanced DAX Concepts
01:11

The DAX language has some important concepts useful to create extremely powerful formulas. This lecture introduce this complex theory, explained in detail in the following lectures.

06:05

What is a filter context and how does it change the values computed by your formulas? This lecture introduces this important concept, setting the foundation for further, more complex expressions.

18:13

CALCULATE works on the filter context and is the only function in the DAX language that is able to create and/or modify a filter context. In this lecture, we go analyze the CALCULATE behavior in deep, explaining in more detail how it works and why it is so important.

08:57

Row context handling is very important whenever you need to perform calculation at the maximum detail level, or when you create calculated columns. This lecture provides a detailed description of the row context and the EARLIER function.

06:52

This lecture shows the behavior of filter and row context when your model contains relationships. It is important to understand what happens to other tables when you put a filter on a column.

06:33

Context transition is the operation automatically generated by CALCULATE that transforms a row context into a filter context. It is very useful but, at the beginning, hard to understand and master. We introduce this concept in this lecture.

08:18

ALLSELECTED is an important function to control calculations using visual totals in a PivotTable. This lecture explains in more details what the ALLSELECTED function does and how it works.

Section 11: Advanced DAX Functions
01:00

DAX has several non-trivial functions. This lecture introduces them and shows a pattern of usage for each one.

18:27

RANKX is an iterator that performs the ranking of a value. Despite its simple syntax, it is a complex function and, in this lecture, we highlight its behavior and warn you against the most common pitfalls

07:20

KEEPFILTERS is useful to change the behavior of context transition introduced by CALCULATE. It is important to know and understand exactly how it works in order to use it when needed. This lecture provides a detailed explanation about what KEEPFILTER does and how it works.

08:29

Some calculations over time require the usage of different aggregation functions. For example, the balance of a current account cannot be summed, because the value at the end of the year is not the sum of the month values. In this lecture, you learn what a semi-additive measure is and how to write one.

06:10

How many orders my company is processing each day? This simple question leads to non-trivial DAX calculations, which we explain in detail during this lecture.

08:40

How to handle aggregations of weeks over months and years? If the week is your basic unit of time, then you cannot use a simple calendar table, you need to learn how to create custom time intelligence calculations and build aggregations over time using a particular calendar table.

Section 12: DAX as a Query Language
17:15

DAX is not only a formula language. You can use DAX to query your data model, showing the result of the query as an Excel table. This lecture introduces DAX as a query language and shows how to populate an Excel table with the result of a DAX query.

10:13

Tables generated by DAX queries can be linked back in Excel, creating very advanced reports that use multi-step calculation by loading data, create tables with the result of a query and then link back the table to the model. This lecture presents some examples of such a technique.

08:38

Once you learned the basics of DAX as a query language, it is time to go to the next level. In this lecture, you learn some advanced functions that will let you create extremely powerful query that will enrich your reports.

Section 13: DAX Patterns
01:28

What is a pattern? Why it is important to learn patterns? This lecture introduces why it is important to understand if you can solve a specific problem by applying an existing pattern, which is a scenario where other experts have already spent time to find the best solution.

06:36

Banding is a common pattern useful to divide data into predefined segments, such as segmentation of customers by total sold, or products by price range.

10:07

The ABC/Pareto analysis aims to detect the core business of a company, making it possible to focus the attention to the important products or customers, in order to maximize the efficiency of any effort (promotion, marketing, investment, and so on).

13:45

Parent-Child hierarchies are unique, because they use a self-join in a table and have no predefined hierarchy depth. This lecture shows how to handle parent-child hierarchies in Power Pivot, using specific DAX functions to this purpose.

10:20

This lecture shows you how to handle data at different granularity levels. For example, budget is monthly and by category, whereas sales are daily and by product. Learn how to correctly shape the data model and write DAX expressions that makes the creation of budgeting report easy and convenient to use.

Section 14: Conclusion
Advanced Course Conclusion
00:18
Section 15: Additional Material
Article

Instructions to download sample database and files.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Marco Russo, Business Intelligence expert at SQLBI - SSAS Maestro & MVP

Marco is a Business Intelligence consultant and mentor. He has been working with Analysis Services since 1999 and with Power Pivot since first beta versions in 2009. He split his time between consulting and teaching at SQLBI, delivering training all around the world, mostly in North America and Europe.

Marco is also a book author and wrote two books for Microsoft Press about Power Pivot with Alberto Ferrari: Microsoft Excel 2013: Building Data Models with PowerPivot and PowerPivot for Excel 2010: Give Your Data Meaning. He is also a speaker at international conferences such as Microsoft TechEd, PASS Summit, SQLRally, and SQLBits.

Instructor Biography

Alberto Ferrari, Business Intelligence expert at SQLBI - SSAS Maestro & MVP

Alberto is a Business Intelligence consultant and mentor. He has been working with Analysis Services since 2005 and with Power Pivot since first beta versions in 2009. He split his time between consulting and teaching at SQLBI, delivering training all around the world, mostly in North America and Europe.

Alberto is also a book author and wrote two books for Microsoft Press about Power Pivot with Marco Russo: Microsoft Excel 2013: Building Data Models with PowerPivot and PowerPivot for Excel 2010: Give Your Data Meaning. He is also a speaker at international conferences such as Microsoft TechEd, PASS Summit, SQLRally, and SQLBits.

Ready to start learning?
Take This Course