Power BI Advanced

Description

This tutor-led online Power BI Advanced Training Course is designed for experienced Power BI users ready to take their skills to the next level. Participants will dive deep into the M language behind the Query Editor, master advanced DAX formulas, and learn to create dynamic, user-responsive reports. The course covers powerful techniques like using parameter tables, building custom data models with calculated tables, and enhancing dashboards with advanced visuals and interactive tiles, equipping learners to create impactful, data-driven insights.

Learning Objectives

• Apply M language to create parameters and perform data transformations in Power Query.
• Use M language transformations like Table.PromoteHeaders and Table.NestedJoin to manage data from multiple sources.
• Build custom functions in Power Query to automate data processing tasks.
• Utilize Power BI Dataflows for centralized data preparation.
• Connect Power BI to SQL Server, optimizing data import and refresh settings.
• Create and manage relationships in data models for robust relational structures.
• Optimize data models with schema design techniques and by hiding unused columns.
• Use DAX to create calculated columns, measures, and advanced data manipulations.
• Apply advanced DAX functions like RANKX and CALCULATE for complex analyses.
• Build and use hierarchies to enable drill-downs and improve data exploration.
• Perform time-based calculations with DAX Time Intelligence functions.

Course Outline

The M Language

Working with M in the Query Editor
The nature of the M language
The let … in statement block
Comments in M
Variables in M
Built-in (primitive) data types
Complex data types

M Language Transformations

M Language Table Functions
Table.PromoteHeaders (Use First Row as Headers)
Table.SelectColumns (Choose Columns)
Table.Combine (Append Queries)
Table.NestedJoin (Merge Queries)
Specifying the Join Kind
Table.ExpandTableColumn (Expand button)

Power Query Custom Functions

Converting a Query to a Function
Connecting to our Example file
Creating a parameter
Invoking a Function
Using the Invoke Custom Function Command
Understanding Function Syntax
Creating a Date of Birth function
Conclusion

Power BI Dataflows

Data Preparation
Using a Dataflow as a Data Source

Mastering SQL Server Connections

Getting Set Up
Comparing the Database to the Data Model
Importing SQL Server Views
Authentication Mode
Enhancing the Data Model
Refreshing Imported SQL Server Data
Import Versus DirectQuery
Refreshing DirectQuery SQL Server Data
Connecting to Database Tables Using SQL Statements
Leveraging Query Folding
Query Folding and Native Queries
Monitoring Query Folding

Managing Relationships

Creating Relationships Automatically
Blocking Automatic Detection of Relationships
Creating Relationships Manually
The Edit Relationship Dialog

Optimizing Data Models

Star and Snowflakes Schemas
Hiding Unused Columns
Sort by Column
Manually Type in Data
Entering Data in Power BI

The DAX Language

Functions
Operators
Literal Values
Object References
Calculated Columns Versus Measures
Creating a calculated column in Power BI
Calculated Columns and Row Context
Calculated Tables
Cloning a table
Using the DISTINCT Function
The CALENDAR and CALENDARAUTO Functions
The FORMAT Function
Creating Measures
Creating a Measures Table
Creating a Measure
Measures and Filter Context

Advanced DAX

X Functions Revision
The RANKX Function
Ranking Within a Group
The CALCULATE Function
The FILTER Function
Using Table Functions
The VALUES Function
The SUMMARIZE Function

Creating Hierarchies

What are hierarchies
Hierarchies and Drilldown
Date Hierarchies
Options Relating to Automatic Date Hierarchies
Manually Creating a Date Hierarchy in Power BI
Creating Business Hierarchies
Creating a Business Hierarchy
Adding Calculated Columns to Support a Hierarchy
The RELATED function

DAX Time Intelligence

Calendar Tables
Power BI Auto Date/Time Feature
Automatic vs Manual Time Intelligence
The TOTALYTD, TOTALQTD and TOTALMTD functions
Example
Specifying End of Fiscal Year
The SAMEPERIODLASTYEAR Function
Using the DATEADD Function for Maximum Flexibility
The PARALLELPERIOD Function
Calculating Running Totals

Private Instructor-Led Online Training

If you have several staff members that need to complete this training, we can arrange a private instructor-led online training course tailored to the requirements of your organisation. Each person is given remote access to a computer in our training centre and provided with lots of opportunities to practice all of the techniques being taught remotely. This means that your staff can attend a course either in the office or from home.