Power BI and Power Pivot for Excel

The term “Data Modelling” is not something that is familiar to most Excel Pros, and that is because data modelling has historically been done by IT on your behalf.  But if you want to be good with Self Service BI using the Microsoft tools, you must learn how to become a data modeller using Power Pivot – this is exactly what you will learn in this class.   Although the concepts are difficult to pick up via self learning, the expert approach from this live training course makes it accessible to any Excel Pro.  At the end of the 2 days you will be well on you way to becoming a Power BI, Power Pivot and Power Query ninja.


Check when this course is next running in your area.

This course is being hosted by an external provider.  Please register here and we will send you some further information.


  1. Understand Power Pivot and how to apply it to your job.
  2. Achieve better and faster results than with Excel alone.
  3. Produce valuable metrics for your business that never existed before.
  4. Learn “recipes” to solve complex problems & create standout work.


  • Intensive users of Excel, particularly those responsible for reporting and analysis.
  • Business leaders who need to mine their data for actionable insight – using existing resources and on short deadlines.
  • Organisations looking to leverage Microsoft’s groundbreaking Power BI offerings.
  • Business Intelligence and Database professionals looking to increase their professional agility and dramatically expand their capabilities.


This course teaches the skills a business analyst needs to know in order to successfully use Power BI, Power Pivot for Excel and Power Query. The skills are completely transferable between Power BI and Excel so there are lots of synergies. The course starts out first using Excel and then moves to Power BI in the middle of day 1.

  1. Importing and creating relationships between your tables of data.
  2. Understanding the optimal data structure to use in Power Pivot.
  3. Writing basic Data Analysis eXpressions (DAX) formulas to extract business insights.
  4. Then writing more advanced DAX formulas that use the power of DAX to extract additional business insights from the data that are not immediately obvious (yet are inherently available).
  5. An introduction and overview of Power Query, how to use the tool and why it is so great.
  6. Helping Excel professionals move from being a Power Pivot for Excel user to being a Power BI user, covering the differences between the tools and how to start using Power BI effectively.


  • Exposure to best-in-class Power Pivot techniques, “recipes”,
  • & practical tips.
  • A digital copy of the book “DAX Formulas for Power Pivot”.
  • USB stick with example workbooks, sample data, and PDF guides.
  • Networking Opportunities



  • Light usage of Excel PivotTables (or similar data analysis tools)
  • Familiarity with Excel functions like SUMIF and VLOOKUP are a plus
  • A passion for acquiring Data Superpowers

(Note: Anyone with Database, BI, Programming, or similar experience are quite qualified to take this course, even if lacking in Excel experience)


Power Pivot requires Excel 2010 or 2013. If you have sufficient Excel experience with Excel 2007 or earlier, you can still succeed in the course, however, to maintain learned skills Excel 2010 or 2013 is necessary when you return to the workplace.


Day One

8:45  Check-in

9:00  Introduction

  • When and Why to Use Power Pivot
  • Tour of the Power Pivot Environment

9:30  Loading Data

  • Various Methods
  • Tradeoffs
  • Best Practices

10:30  1st Break

10:45  Introduction to Formulas and DAX using Power Pivot for Excel

  • Named reference syntax
  • Similarities to traditional Excel
  • Measures (Calc Fields) vs. Calc Columns
  • Best practices

12:30  Lunch

1:00  Working with Multiple Tables using Power BI

  • Relationships: the End of VLOOKUP?
  • Data Tables vs. Lookup Tables
  • Multiple Data tables in a single model
  • Troubleshooting your “relationship problems”
  • Exercises to cement your learning

2:30  2nd Break

2:45  The Magic of DAX Measures using Power BI

  • The CALCULATE() function.
  • ALL(), percentage of totals, and “canceling slicers.
  • Introduction to Time Intelligence: Date/Calendar Tables
  • Running totals – Year to Date,
  • Change versus Prior Month/Year Etc.

4:00  What-if Analysis using Disconnected Tables and Slicers

5:00  End of Day One

Day Two

9:00  Recap and More DAX

  • Recap of previous day
  • Theory of the Vertipaq engine
  • Filter() , Custom Calendars.

10:30  Break

10:45  DAX Patterns for common problems using Power BI

  • Counting things that didn’t happen (eg customers without purchases)
  • Custom time intelligence patterns

11:30  Power Query

  • When to use it and why
  • Built-in transformations
  • The “M” language
  • Using Power Query to solve real world problems.

12:30  Lunch

1:30  Performance

  • How to Keep Your Files Small and Your Calculations Fast
  • Theory behind compression
    Sharing and Refreshing Workbooks
  • Different Web Hosting options (SharePoint and Power BI)
  • Options to refresh workbooks

1:45  Power BI Visuals

  • A detailed review of what is different about Power BI vs Excel.
  • Learn to think differently and use the visualisations available.

3:00  Break

3:15  Power BI Service

  • A detailed review of the cloud offering including sharing, Q&A, Analyze in Excel, Dashboards

3:45  Excel’s Best Kept Secret Unveiled

  • Cube Formulas

4:00 General Q&A

  • Discuss your specific business problems and anything else.

5:00  End of Day Two

This comprehensive two day workshop is $1,495 plus GST.

This course is being hosted by an external provider.  Please register here and we will send you some further information.

[we_table style=”1″ show_time=”0″ orderby=”upcoming” tag=”Power BI” count=”50″]

Prefer to take this course online, click below to sign up online or find out further information