SE flag SE
+46 20 109 324
+46 20 109 324

Designing Data Models in Excel for Power BI Reports Training

Course 1362 Advantage Plan Course

  • 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

    Prerequisites

    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

    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

    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

    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

    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?
    • jul 15 - 17 15:00 - 22:30 CEST
      Herndon, VA or Virtual
    • 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
    Speak with a Learning Consultant
    * Required Fields
    Speak with a Learning Consultant

    Fill out the form below

    * Required Fields

    Need Help Finding The Right Training Solution?

    Our training advisors are here for you.

    Contact Us

    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.