Course Overview

This 4-day course is intended for anyone new to designing and implementing self-service business intelligence solutions with Microsoft Power BI Desktop and the Power BI Service. The course structure is very comprehensive and provides a total immersion in the subject matter. The training is delivered with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. Also, as Power BI is continually evolving, this course is regularly reviewed and updated to keep the content as fresh and relevant as possible, thus ensuring maximum productivity from the learning experience. Due to the very comprehensive nature of this course, the content delivered may vary depending on new and updated features, and client requirements.

The course structure takes a broad and practical approach to Power BI, defining best practices and demonstrating useful techniques that will prove extremely useful in the workplace. The content delivered will also help delegates prepare for the “PL-300 Microsoft Power BI Data Analyst” exam.

The first part of the course introduces the capabilities and components of Power BI before delving into the realms of discovering and loading data from a variety of sources. Primarily focussed on using Power Query to create robust queries, students will learn about issues surrounding data quality, how to cleanse data, how to transform and then restructure data, and how to mashup data from various sources by merging and appending data. We will also examine how to manage, structure, and parameterise queries. Although a lot can be achieved working with the user interface in Power Query, we will also delve behind the scenes and introduce the M language, highlighting ways in which existing code can be tweaked and how user defined code and functions can be implemented.

Part two of the course introduces students to tabular data modelling concepts, preparing the data in readiness for use in reports and dashboards. The importance of implementing relationships is also covered. In order to enhance the data model, students will also learn about the DAX expression language to enable them to implement calculated columns, measures, and calculated tables. Consideration is also given to implementing time intelligence in the design of data models.

In part three, you will learn how to create and design reports and implement visuals. We will first look at text-based visuals before moving on to graphical components like charts, gauges and KPIs (Key Performance Indicators). The implementation of maps in reports is also given coverage. To create interactivity in reports, students will also learn how to implement various types of filters and slicers.

Finally, in part four, we initially look at how we can further enhance the user experience by implementing features such as drill through reports, bookmarks, and showing and hiding content. Then, in order to share reports created in Power BI Desktop, we turn our attention to the Power BI Web Service. Attendees will learn how to publish reports to the Power BI Service and how to organize and secure content. The creation and use of dashboards will be considered, and different ways of sharing content will also be given coverage. We will look at user accessibility issues and consider how users, including mobile users, can interact with report content. Managing and refreshing datasets in the Power BI Service is also included.

Additionally, with the course structure being fully modularised, customised versions of this course can be devised and delivered to suit individual requirements – just ask for details.

Prerequisites

• • This course varies in complexity ranging from introductory level to advanced and should therefore be considered a technical course. Attendees should be able to demonstrate a good level of computer literacy and skills. There are some elements of coding involved in the training and delegates should feel comfortable with basic coding concepts as might be the case from working with macros in Microsoft Excel or other alternate functional/scripting language.
    
    You will have a good understanding of your business needs to collect, cleanse, and analyse data, in order to be able to visualise and gain insights into that data.
    
    Customised versions of this course can be devised and delivered to suit individual requirements, please ask for details.
    
    Virtual Classroom Events
    When attending this course as a virtual classroom even you will need:
    The Software being training on installed on your laptop or PC. It is assumed that you will have the latest desktop version of the software installed.
    Two screens are essential.
    
    

Course Content

• Part 1: Introduction and Power Query

  1 Introducing Power BI
  Describe the Power BI Products and Services
  Introducing Power BI Desktop
  Power BI Licensing
  2 Loading Data
  Load Data from Text Files
  Load Data from Excel Workbooks
  Load Data from Microsoft SQL Server
  Comparing Import and DirectQuery Storage Modes
  Load Data from OneDrive and SharePoint
  Load Data from the Internet
  Connecting Live to a Published Dataset
  3 Transforming Data
  Profile Data
  Cleanse Data
  Transform Data
  Query Folding
  Shape Datasets
  Merge and Append Data
  Fuzzy Lookups
  Structure and Manage Queries
  View Query Dependencies
  Power Query Parameters
  Power BI Template Files
  Introduction to the M Language
  Create a User Defined Function with M
  

• Part 2: Data Modelling and DAX

  4 Data Modelling
  Basic Data Modelling Concepts
  Fact and Dimension Tables
  Configure Table and Column Properties
  Categorise Data
  Sorting Columns
  Create Hierarchies
  Group and Bin Data
  Organise Content with Display Folders
  Hiding Columns and Tables in Report View
  Create and Manage Relationships
  Understand Filter Propagation
  5 The DAX Expression Language
  Introduction to DAX
  Extend the Data Model with Calculated Columns
  Create Custom Groups
  Add Measures to the Data Model
  Implicit v Explicit Measures
  Manipulate the Filter Context with CALCULATE
  Use Variables
  The DAX Query View Environment
  Iterator Functions
  Using the FILTER Function
  Create Calculated Tables
  Create a Date Table
  Analyse Data over Time (Time Intelligence)
  Suppress the Display of Totals in a Visual
  Display Selected Filter Values in a Visual
  Dynamic Format String

• Part 3: Reports and Visuals

  6 Visualising Data
  Implement and Format Table and Matrix Visuals
  Rename Fields in Visuals
  Show Items with no Data
  Conditionally Format Visual Content
  Use Card Visuals
  Implement and Format Charts
  Report Themes
  Use the Small Multiples Feature
  Sparklines
  Use Tooltips
  Implement Reports as Tooltips
  Continuous v Categorical Axis
  Automatically Find Clusters of Data
  Animate Scatter Charts
  Use the Analytics Pane
  Forecasting & Anomaly Detection
  Use the Analyse Feature
  Tree Maps
  Analyse Data with a Decomposition Tree
  Gauges and KPIs
  Implement Maps
  Resolve Location Errors in Maps
  Implement Slicers
  Visual, Page, and Report Level Filters
  Synchronise Slicers across Pages
  Cross-Filtering and Cross-Highlighting
  Import Visuals from Microsoft AppSource
  

• Part 4: Enhancing and Publishing Reports

  7 Enhancing the User Experience
  Drillthrough Reports
  What-If Parameters
  Field Parameters
  Implement Bookmarks
  Implement Buttons
  Text Boxes, Shapes, and Images
  Page & Bookmark Navigation
  Show and Hide Content
  Implement and Manage Q&A
  Smart Narrative
  Mobile Report Layout
  Useful External Tools and Resources
  8 The Power BI Web Service
  Introduction to the Power BI Web Service
  Publish Reports to the Power BI Web Service
  Manage and Organise Content in Workspaces
  Workspace Folders
  Create and Manage Dashboards
  Dashboard Themes
  Set up Data Alerts
  Share Content and Workspaces
  Create and Distribute Apps
  Secure Data with Row Level Security (RLS)
  Refresh Datasets
  Understand Data Gateways
  Export Reports
  

Included with this course

• • Comprehensive course materials and exercises