Designing Data Models in Excel for Power BI Reports Training

Level: Intermediate
Rating: 4.7/5 4.71/5 Based on 63 Reviews

This course focuses on designing BI Data Models to display in either an interactive Power BI report or in an interactive Excel dashboard. This advanced Microsoft Excel/fundamental Power BI course will take you through a data journey from importing, cleansing and merging large amounts of data to analysing the data to presenting strategic data in a Power BI dashboard. This is a practical course designed to provide flexibility and to empower you to choose how you want to present your business insights to help management make informed decisions.

Key Features of this Power Pivot Training:

  • After-course instructor coaching benefit
  • Learning Tree end-of-course exam included
  • After-course computing sandbox included

You Will Learn How To:

  • Transform and present sophisticated data to provide Business Intelligence (BI)
  • Actualize a data model from multiple sources
  • Shape and filter tables to prepare for analyses by fixing common issues
  • Mine and expose hidden information with Data Analysis eXpressions
  • Communicate business and data insights with Power BI reports and dashboards

Choose the Training Solution That Best Fits Your Individual Needs or Organizational Goals


In Class & Live, Online Training

  • 2-day instructor-led training course
  • One-on-one after-course instructor coaching
  • After-course computing sandbox
  • Tuition can be paid later by invoice -OR- at the time of checkout by credit card
View Course Details & Schedule

Standard $2225 CAD

Government $1950 CAD




Team Training

  • Bring this or any training to your organization
  • Full - scale program development
  • Delivered when, where, and how you want it
  • Blended learning models
  • Tailored content
  • Expert team coaching

Customize Your Team Training Experience


Save More On Training with FlexVouchers – A Unique Training Savings Account

Our FlexVouchers help you lock in your training budgets without having to commit to a traditional 1 voucher = 1 course classroom-only attendance. FlexVouchers expand your purchasing power to modern blended solutions and services that are completely customizable. For details, please call 888-843-8733 or chat live.

In Class & Live, Online Training

Time Zone Legend:
Eastern Time Zone Central Time Zone
Mountain Time Zone Pacific Time Zone

Note: This course runs for 2 Days

  • Feb 7 - 8 9:00 AM - 4:30 PM EST Ottawa / Online (AnyWare) Ottawa / Online (AnyWare) Reserve Your Seat

  • Apr 25 - 26 9:00 AM - 4:30 PM EDT Herndon, VA / Online (AnyWare) Herndon, VA / Online (AnyWare) Reserve Your Seat

  • Aug 8 - 9 9:00 AM - 4:30 PM EDT Ottawa / Online (AnyWare) Ottawa / Online (AnyWare) Reserve Your Seat

Guaranteed to Run

When you see the "Guaranteed to Run" icon next to a course event, you can rest assured that your course event — date, time — will run. Guaranteed.

Important Power Pivot Course Information

Power Pivot Course Outline

  • Identifying the Role of Power Pivot in Business Intelligence (BI)

    Introducing the Business Intelligence Tools

    • Exploring the Microsoft Business Intelligence tools
    • Defining the requirements for a collaborative solution
    • Identifying the steps to create a Power BI dashboard

    Familiarisation with Power Pivot

    • Navigating the Power Pivot interface
    • Extracting information from data with Power Pivot
    • Uncovering data interpretation issues
    • Creating a dashboard
  • Building the Data Model with Power Pivot

    Defining a consolidated view of data

    • Generating a data mashup from structured and unstructured data sources into a data model
    • Deriving relationships from data sources with the Relationships tool and the Diagram View

    Denormalising data to simplify usage within other BI reporting tools

    • Acquiring data from related tables
    • Defining calculated columns
    • Consolidating information available to BI tools

    Querying SQL Server data

    • Designing queries to import data from SQL Server
    • Relating tables with outer joins

    Fixing common data issues with Power Query

    • Extracting, Transforming and Loading (ETL) data
    • Converting data formats with Power Query steps
    • Parsing columns to aid analysis
    • Removing duplicates from a data set
    • Constructing a single data set from multiple sources with the same field headings
  • Manipulating and Analysing Data with the Data Analysis eXpressions (DAX) Language

    Defining measures for business performance

    • Distinguishing the role of measures
    • Translating key business concepts into measures
    • Providing context for measures within a PivotTable
    • Determining between implicit and explicit measures

    Implementing DAX functions in Power Pivot

    • Expressing information with measures
    • Exposing hidden information from data
    • Troubleshooting and debugging DAX calculations

    Exploiting data analytics with aggregation

    • Quantifying and mining information with DAX functions
    • Summarising and aggregating data from other tables with the X functions
    • Evaluating expressions with the CALCULATE() function and filter functions
    • Substituting values with the SWITCH() function

    Mining for information with date and time analysis

    • Grouping dates for time analysis
    • Comparing and categorising time periods with Time Intelligence functions

    Setting key business targets with KPIs

    • Analysing performance with measures
    • Gauging performance against goals
  • Designing effective Power BI dashboards

    Familiarisation with the Power BI Architecture

    • Deciding on where to develop a dashboard
    • Importing an Excel data model into Power BI Desktop
    • Developing a data model in the Power BI Desktop
    • Crafting visualisations from the analytics

    Building a Power BI dashboard

    • Constructing Power BI Reports
    • Planning a Power BI Dashboard
    • Assembling a Power BI Dashboard

Team Training

Power Pivot Training FAQs

  • Why are you using Excel to develop your data models?

    Most participants attending a Power BI have worked in Excel. The Excel BI tools are the same as those found in Power BI. Thus, this course enables participants to expand their Excel knowledge and develop analytics that can be used in both Excel or Power BI.
  • How much time will we spend on developing Reports?

    About 2 hours. This course will provide a fundamental understanding on how to develop visualisations in Power BI. For those who are Power Excel users, this will be like producing charts.
  • What version of Excel is this course applicable to?

    The current version (2010) is used in this course.

Questions about which training is right for you?

call 888-843-8733
chat Live Chat

Why do we require your location?

It allows us to direct your request to the appropriate Customer Care team.

100% Satisfaction Guaranteed

Your Training Comes with a 100% Satisfaction Guarantee!*

*Partner-delivered courses may have different terms that apply. Ask for details.

Ottawa / Online (AnyWare)
Herndon, VA / Online (AnyWare)
Ottawa / Online (AnyWare)
Why do we require your location?

It allows us to direct your request to the appropriate Customer Care team.

Preferred method of contact:
Chat Now

Please Choose a Language

Canada - English

Canada - Français