Designing Data Models in Excel for Power BI Reports Training

Duration: 3 days
Labs: Yes
Language: English
Level: Intermediate

This course focuses on designing BI Data Models to display in either an interactive Power BI report or an interactive Excel dashboard. This is both a fundamental Power BI course and an advanced Excel course that 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 PivotTable and a Power BI dashboard. This practical course provides flexibility and empowers you to choose how you want to present your business insights to help management make informed decisions.

Data Models in Excel for Power BI Delivery Methods

In-Person
Online

Data Models in Excel for Power BI Course Benefits

Transform and present sophisticated data to provide Business Intelligence (BI)
Actualise a data model from multiple sources with Power Pivot
Shape and prepare your data with Power Query
Mine and expose hidden information with Data Analysis eXpressions
Communicate business and data insights with Power BI reports and dashboards
Leverage continued support with after-course one-on-one instructor coaching and computing sandbox

Data Models in Excel for Power BI Outline

Important course information

Prerequisites

Experience with Excel at the level of:
- Excel Data Analysis Course • course 195
This course is applicable to all versions of Excel, from Excel 2010 to Excel 2021

Module 1: 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 simple dashboard

Module 2: 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

Denormalizing data to simplify usage within other BI reporting tools

Simplifying the data model
Acquiring data from related tables
Consolidating information with calculated columns

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

Module 3: Shaping Data with Power Query

Familiarisation with Power Query

Navigating the Power Query interface
Extracting, Transforming, and Loading (ETL) data
Converting data formats with Power Query steps
Parsing columns to aid analysis

Combining Data

Merging tables of data
Constructing a single data set from multiple sources with the same field headings
Develop an automated solution to combining data

Filtering Data with Parameters

Developing queries
Removing duplicates from a data set
Defining Excel tables as parameters for a Power Query

Module 4: Manipulating and Analyzing 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

Module 5: Designing Interactive Power BI dashboards

Familiarisation with Power BI

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 visualisations
Designing visual-level, page-level, and report-level filter
Assembling a Power BI Dashboard

Commercial Pricing

19 530 kr

3-day instructor-led training course
Hands-on labs included
End-of-course Learning Tree exam included
After-course coaching available
After-course computing sandbox

Would you like to take this course as a team?

Include dates with afternoon start times

Guaranteed-to-Run - You can rest assured this class will not be canceled. Delivery format is subject to change – please call Customer Service if you have any questions.
sep 9 - 11 15:00 - 22:30 CEST

Herndon, VA or Virtual
nov 4 - 6 15:00 - 22:30 CET

Herndon, VA or Virtual
jan 13 - 15 15:00 - 22:30 CET

Herndon, VA or Virtual
mar 10 - 12 15:00 - 22:30 CET

Herndon, VA or Virtual
maj 12 - 14 15:00 - 22:30 CEST

Herndon, VA or Virtual

Speak with a Learning Consultant

Inquiring For

Myself

Team/Group

First Name*

Last Name*

Email*

Phone Number*

Company*

Job Title

I'm interested In

* Required Fields

Need Help Finding The Right Training Solution?

Our training advisors are here for you.

Data Models in Excel for Power BI FAQs

The Power Pivot, Power Query, and Power View were first developed for Excel 2010 in 2012. These tools were later integrated into Power BI in the year 2015. This course enhances your Excel skills and shows you how to quickly transfer those skills to Power BI to create a data model.

About 2.5 days. An essential part of Business Intelligence is to analyse the data so that it can provide information to make an informed decision. This course invests time to develop the data model from tables and write simple design measures used to analyse and present data in a PivotTable, Pivot Chart, and a Power BI report.

About ½ a day. This course will provide familiarisation with the Power BI interface to develop visualisations in Power BI. The main focus will be to familiarise you with the tools so you can easily explore them further after the course.