Data analysis and analytics. Uncover insights and transform your organisation.

This Power BI course will develop your data modelling skills, widening the scope of your Business Intelligence processes and enabling you to extract more valuable data from your reports and dashboards.

While connecting to and importing from multiple sources simultaneously, we will overcome relational database obstacles and aggregate tables using a variety of advanced data modelling techniques and analysis expressions.

You will also expand your time-intelligence and relationship function vocabulary to refine your DAX routines, resulting in improved efficiency of your queries.

Finally – when working with large datasets, it is important that your data connections and query scripts do not burden your computer’s resources. We will show you methods and tools to reduce memory and CPU consumption and optimise your Power BI models.

Aims

We want to expand the capability of your Power BI reports by equipping you with the knowledge and skills to connect and model your tables with the most efficient and flexible queries, while optimising resource consumption. You will also learn to implement Data Analysis Expressions that create and filter table relationships, and build period calculations using time-intelligence functions.

Outcomes

By the end of this course, you should be able to:

• identify and implement the best connection/storage mode for your data sources
• connect to multiple data sources, including cloud servers
• model many-to-many relationships and bidirectional filters
• create aggregated tables
• create custom hierarchies
• create physical and virtual relationships
• use a variety of DAX functions to look up and filter tables
• build and compare time-intelligent DAX functions
• improve DAX efficiency with measures, iterator functions and declared variables
• optimise performance with query folding and custom program settings.

Content

Enhance Business Intelligence Process Planning

• Overview of Data Modelling
• Efficient Data Strategy
• Columnar and Row Databases
• Data Sources
• Data Models in Power BI

Import and transform data from multiple sources simultaneously

• Data Connections in Power BI
• Excel and CSV Files
• Website Live Tables
• Connecting to a Folder
• Connecting to a SharePoint List, Folder, Online List
• Connecting to SQL Server Tables
• Connect to Microsoft Azure
• Connect to a Power BI dataset

Advanced Data Modelling

• Table Storage Modes
• Relationships in Power BI
• Create Aggregate Tables
• Create Custom Hierarchies
• Power BI Data Model

Model Relationships with DAX

• Physical and Virtual Relationships
• USERELATIONSHIP
• LOOKUPVALUE
• FILTER
• MAX and MIN
• INTERSECT, TREATAS, EXCEPT, UNION

Advanced DAX Time Intelligence Functions

• DAX for time intelligence
• PREVIOUSMONTH, PREVIOUSYEAR
• NEXTMONTH, NEXTYEAR
• DATESINPERIOD, DATESQTD, DATESYTD
• LASTDATE
• SAMEPERIODLASTYEAR, PARALLELPERIOD

Improve DAX Efficiency

• Improve Power BI Performance by Optimizing DAX
• Handling Errors Using DAX
• Use Iterator Functions
• Declare Variables Using VAR

Program and file optimisation

• Optimising your Power BI import model
• Disable intermediate queries
• Disable Auto date/time
• Optimise Column Data Types
• Group By and Summarize
• Remove Unnecessary Rows
• Error Handling in Power Query
• Performance Analyzer
• Leverage Query Folding

Intended audience

Anyone interested in data modelling and visualisation.

Prerequisites

It is recommended that you are comfortable with the learning outcomes equivalent to our Power BI Course: Level 2A, and of Microsoft Excel Course: Level 2. In particular, the use of Tables.

Delivery modes

• Face-to-face, presenter-taught training
• Online training via the platform Zoom

Delivery style

Face-to-face classes (CCE, Newtown)

These classes run in a computer lab and you do not need to bring your own device.

Face-to-face classes (external venue including PARKROYAL, Cliftons and CBD)

These classes run in a classroom and you need to bring your own device with a Windows operating system.

Online classes

You will need your own device with a Windows operating system. Power BI desktop only runs on a Windows operating system. It does NOT operate on Mac.

Materials

A course workbook and exercise files are distributed electronically using Dropbox.

Before the course

Please ensure Power BI Desktop is installed on your device before class. It can be downloaded from the Microsoft Website. Recommended minimum version – July 2022. 

You will be provided with a login to Power BI pro – the cloud application. However, you can choose to use your own Power BI pro license if you have one.

Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.