Designing Data Models in Excel for Power BI Reports Training

Course 1362

  • Duration: 3 days
  • Labs: Yes
  • Language: English
  • 11 NASBA CPE Credits
  • Level: Intermediate
Get This Course $2,650
  • 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

#1362
  • Guaranteed to Run - you can rest assured that the class will not be cancelled.
    Aug 8 - 10 9:00 AM - 4:30 PM EDT
    Ottawa or AnyWare
  • Oct 26 - 28 9:00 AM - 4:30 PM EDT
    Herndon, VA or AnyWare
  • Jan 30 - Feb 1 9:00 AM - 4:30 PM EST
    Ottawa or AnyWare

This course focuses on designing BI Data Models to display in either an interactive Power BI report or in 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 analyzing the data to presenting strategic data in a PivotTable and a Power BI dashboard. This is a practical course designed to provide flexibility and empower you to choose how you want to present your business insights to help management make informed decisions.

Note: This course will be 3-Days instead of 2-Days beginning June 8th, 2022.

    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)

    • Actualize 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

    Prerequisites

    Note: This course will be 3-Days instead of 2-Days beginning June 8th, 2022.

    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

    Familiarization with Power Pivot

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

    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

    Familiarization 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

    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
    • Summarizing 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 categorizing time periods with Time Intelligence functions

    Setting key business targets with KPIs

    • Analyzing performance with measures
    • Gauging performance against goals

    Familiarization 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 visualizations from the analytics

    Building a Power BI dashboard

    • Constructing Power BI visualizations
    • Designing visual-level, page-level, and report-level filter
    • Assembling a Power BI Dashboard

    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 the year 2012. These tools were later integrated into Power BI in the year 2015. This course enables you to enhance your Excel skills and shows you how to easily transfer those skills to Power BI to create a data model.

    About 2.5 days. The most important part of Business Intelligence is to analyze 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 design simple measures that can be used to analyze and present data in both a PivotTable, Pivot Chart, and a Power BI report.

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

    Chat With Us