Day One

Introduction

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

Loading Data

• Various Methods
• Tradeoffs
• Best Practices

Introduction to Formulas and DAX using PowerPivot

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

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

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.

What-if Analysis using Disconnected Tables and Slicers

Day Two

Recap and More DAX

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

DAX Patterns for common problems using Power BI

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

Power Query

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

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

Power BI Visuals

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

Power BI Service

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

Microsoft’s Best Kept Secret Unveiled

• Cube Formulas