Designing Data Models in Excel for Power BI Reports Training

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