Power BI – intermediate (1 day)

Power BI – intermediate – overview

This course is designed for those already using Power BI Desktop and are ready to work with further analytical elements of Power BI reports. This course maintains a balanced look at all parts of analysing data including the Query Editor to transform data, a deeper dive into DAX measures, data modelling, and enhanced dashboard visuals.

Full details below or download course outline.

Learning objectives

The aim of this course is to go beyond the basics and explore Power BI’s analytics capabilities in more detail, working with examples that take you to the next level in your use of Power BI.

Who should attend?

Managers and others who need to use or create reports and tables to support business management and decision-making.

Pre-requisites

Our assumption is you are already using Power BI and are ready to move beyond the basics. If not, we recommend attending the Power BI Introduction course first.

Course format

A very practical, interactive one-day session for a maximum group size of 8. Comprehensive materials provided, including exercise files and a unique step-by-step guide developed by the trainer specifically for this programme.

This programme is also delivered virtually.

Expert trainer

Alan is a highly experienced and very popular IT skills trainer and a specialist in Power BI. After eighteen years as an employed IT trainer and training manager he went independent in 2013, specialising in Microsoft Office courses, primarily Power BI, Excel, PowerPoint, Word, Outlook and Access, although he also delivers training in other programs when required. His clients come from all sectors and include such organisations as The Economist, Hyundai, Marston Group, MediaCom, London Borough of Harrow, Paragon Customer Communications, ICP Global Creative, Bywaters, Amsafe Bridport, Seetec, Trumpf, Hyundai, etc, etc.

See what some of the participants have said about the workshops he’s delivered for us:

‘Fantastic. Well-paced and easy to follow.’
‘Great experience.’
‘Very personable, relatable, patient and able to present complicated processes in an understandable way.’
‘Friendly and good at checking people are up to speed.’
‘Very good and explained and answered all questions.’
‘Explains everything well.’
‘Excellent teacher and very intelligent.’
‘Very knowledgeable would love to have him again if there is another course – thank you.’
‘Alan explains things very clearly and addresses issues on questions before they are visible. Thank you very much!’

Power BI – intermediate – course outline

1 Connecting to data

  • Connecting to databases
  • Import vs. Direct Query
  • Connect to a folder of files
  • Managing file connections

2 Transforming tables

  • Add a custom column
  • Appending tables together
  • Add an index column
  • Fixing error issues
  • Perform maths operations

3 The data model

  • Model relationships
  • Managing relationships

4 Merge queries

  • Table join kinds
  • Merging tables

5 Conditional and custom columns

  • The M language
  • Add a conditional column
  • Custom column formula
  • Merging columns
  • Creating M functions
  • Query group folders
  • Write an IF function

6 Pivot and group tables

  • Pivot a table
  • Pivot but don’t summarise
  • Unpivot tables
  • Grouping table columns
  • Append mismatched headers

7 More about data modelling

  • Data model relationships
  • Mark a table as a date table

8 Two types of DAX formulas

  • New columns and measures
  • Creating new column calculations
  • The SWITCH function
  • Using TRUE in a SWITCH function

9 Writing DAX measures

  • Creating DAX measures
  • Control field summarisation
  • Data classification
  • COUNTROWS and DISTINCTCOUNT functions
  • DAX rules

 10 The CALCULATE measure

  • Filtering with CALCULATE
  • Things of note about CALCULATE

 11 The SUMX measure

  • The X-iterator functions
  • Anatomy of SUMX
  • Writing a SUMX
  • When to use X-functions

 12 Time intelligence measures

  • Importance of a calendar table
  • Create a TOTALYTD measure
  • Changing a fiscal year end date
  • Use DATEADD to compare historical data

 13 Dashboard visualisations

  • Creating and formatting charts
  • Utilising report themes
  • Control a field’s number format
  • Filtering across report pages
  • Import= online visuals
  • Create hierarchies to drill layers
  • Compare data in groups
  • Adding conditional formatting

 14 Publish and share reports

  • Get quick insights
  • What is Q&A?
  • Pinning visuals to a dashboard
  • Sharing dashboards
  • Exporting dashboards