AUCKLAND (09) 378-0411       HAMILTON (07) 838-0700       WELLINGTON (04) 472-4830      CHRISTCHURCH (03) 365-5020      |      
Home Courses Masterclass Power BI and Modern Excel


Masterclass Power BI and Modern Excel

Length: 1 Day    Cost: $0 + GST    Version: Power BI

= Scheduled class    = Guaranteed to run    = Fully booked
Click on the dates to book online

Can't find a class in your area? Contact our sales team and request a class date.
Not sure about the version of your software? Click here to find out.
Want to know if this course is right for you? Assess your skills level with our online Skills Assessment tool.

About this Course

Two insightful days uncovering all you need to know about shaping, loading and modelling data in Excel and Power BI. This exclusive event is only available in Wellington, presented by two Microsoft MVP's Ken Puls and Matt Allington.

Day One : Dimensional Modelling - Learn how to prepare and structure your data for a successful Excel and Power BI model that will stand the test of time.

Day Two : DAX Formulas - Explore the incredible DAX language to summarise virtually and statistic you want to know, allowing you to gain real insight from your data.


Data professionals who are responsible for building intelligence reports, whether you use Excel or Power BI.


At a minimum, participants must have experience using PivotTables. Exposure to Power Pivot is an asset, but not required. It is also receommended for Day two that participants have experience writing DAX formulas for 6-18 months. It is not necessary to have an in depth knowledge of how the formulas work - that is what will be covered in the course.

At Course Completion

Developed by a CPA with real world experience who is also a renowned Business Intelligence expert, this course is intended to teach you the right way to build solid and scalable dimensional models.

You'll learn key concepts and terminology around data warehousing and dimensional modelling including Facts, Dimension, Relationships, Schemas, keys and more. You'll learn why the PivotTable has been "too helpful" and how it can impact your ability to extend your data models.

Armed with the background theory on modeling, it's then time to get your hands dirty with hands-on examples of solving several "many to many" join problems. From composite keys to bridge tables, slowly changing dimension to flattening snowflakes, you'll learn which tools and techniques to use and when.

This is an exciting course, as it pairs two of Excel’s most powerful features (Power Query and Power Pivot) together in one place, using each for what they were designed to do. You’ll leave armed with not only the experience, but handy reference cards to apply these techniques to your own data, and to determine “Is this a Power Query job, or a DAX job?”

And the best part of all of this? The material in this course is 100% portable to Power BI as well. You’re not learning for one program, but rather for two!

Course Outline

Day 1 - Dimensional Modelling

  • The importance of importing data to Power Pivot with Power Query
  • Review of the core Power Pivot benefits
  • Dimensional modelling terms and techniques
  • Solving common join problems when linking tables
  • Creating calendar tables on the fly with Power Query
  • Linking tables with different date granularity
  • Dealing with slowly changing dimension tables
  • Data optimization rules to keep your models performance

Day 2 - DAX Formulas

The DAX language is similar to Excel functions in many ways, but it also has many important differences. Before you can become proficient in the DAX language, you simply must understand how the Power BI/Power Pivot database engine works.

  • You will be provided with a voucher at lunchtime which can be redeemed at the food court downstairs.

 Print this page