Intermediate Power BI (session 1)

Power BI
intermediate
Intermediate Power BI course
Published

September 10, 2025

This is part of our intermediate-level Power BI desktop course. The course structure is as follows:

NoteSession materials

Most of the data is taken directly from source, but there is one subsidiary dataset with CCTV camera locations. These are best-guesses about likely locations, and should be used purely for demonstration purposes.

Previous attendees have said…

  • 78 previous attendees have left feedback
  • 99% would recommend this session to a colleague
  • 95% said that this session was pitched correctly

NoteThree random comments from previous attendees
  • Brendan is an excellent teacher - very grateful. Due to time being spent helping, it felt that practical tasks were a bit rushed where some more theory around it would have been nice. I didn’t take as many tips/notes from this sessions as I have in previous ones. I was very interesteed in the model and DAX sections and would liked to have spent a little more time in here today, however, I will look forward to the next session for that and will do my homework. thank you brendan! :)
  • Is it worth doing a pre-filtering of the course, pointing them to the beginner course again if they don’t know certain things, or a 15-minute overview and if users aren’t familiar with beginner details then ask them to leave for an appropriate level course or not ask questions? I would say using Query Bar is Beginner level and not Intermediate. If people don’t know how to use Query Bar (deleting a step shouldn’t on an intermediate course as it is taught at beginner level?) If users aren’t able to spot simple data issues from quickly scanning the preview pane, then should they be on the course. It felt like Brendan wasted 45-50 minutes for what was basic stuff that some of the users didn’t know, with him keeping his patience admirably. However, I felt they shouldn’t have been on the Intermediate level course if they had to ask those questions.
  • Some of the time it was very fast paced - which for me was fine because I had already done these things in my day to day work but I think for people who maybe hadn’t it would have been hard to keep up.
TipForthcoming session(s)
Booking link Date
Intermediate Power BI (session 1) 10:00-12:00 Tue 28th April 2026

Session outline

  • load some data
  • wrangle it with PowerQuery
  • write some DAX and think about the filter context
  • build some visuals and assemble them into a dashboard

Session aim

  • recap and reminder about core Power BI workflows Completed dashboard

Data

Please import the dataset into Power BI

  • Get data > From web
  • rename csv?layers=0 to cctv Rename to cctv
  • then Transform Data

PowerQuery reminder

  • aimed at data wrangling
  • the right place to do any one-off calculations and transformations
  • reminder about terminology:
    • query = dataset
    • query steps = steps in wrangling from loading to final data
    • PowerQuery M (PQM) language

Fix the dates

  • split [Hour] on “-”, then rename to give [start_hour] and [end_hour] cols
  • make sure [Date] is a date, rather than a date/time
  • custom columns to make [start_time] by appending with &
  • [Date] & [start_hour]
  • duplicate and tweak to make [end_time]

Rename and pivot the counts

  • unpivot the three Number_of_xxx columns
  • then replace values to remove Number_of_ to leave Bicycles, People and Vehicles
  • you could also tidy Road vehicles
  • rename to Type and Count

Duplicate query

  • just to see what happens
  • then close and apply

New query in PowerQuery

Relationships

  • go to model view
  • drag dundee_cctv_locations[Source] to cctv[Source]
  • and cross-filter direction set to both

Sorting days

  • still in model view, use DAX to make a [weekday] column with weekday = WEEKDAY(cctv[Date], 2)
  • n.b. quirky return value argument
  • then, in the model, sort [Day] by [weekday] in Properties > Advanced

Date table

  • Power BI does lots of helpful inference about dates
    • e.g. the .[Year], .[Month], etc values that pop up when writing functions about a date column in DAX
  • those work best when the dates are continuous. We have some missing data here, so we’ll add a proper date table
  • New Table, then date_table = CALENDAR(min(cctv[Date]), max(cctv[Date])) - creates a row per day in the range of dates
  • we’ll talk more about date tables in the next session

DAX query view

This is a new way of building and testing DAX expressions without having to add them to your model/report. As of September 2025, it’s available to about 75% of staff in the network (from our informal polling). You might update your Power BI desktop if you’d like to try it. You can find it in the left-hand menu bar: DAX query view

  • EVALUATE() as a wrapper to any DAX expression, allowing you to run it in the DAX query view
  • F5 to run
  • .[Year] syntax
  • SUMMARIZECOLUMNS(group, name, expression)

Summary tables

  • total counts by day, and by type
  • SUMMARIZECOLUMNS(cctv[Date].[Date], "Total count", SUM(cctv[Count]))
  • SUMMARIZECOLUMNS(cctv[Type], "Total count", SUM(cctv[Count]))
  • please now translate each to new summary tables

Dashboard build out

Completed dashboard

Dashboard build out

  • 2 x filters
  • 1 x map
  • 1 x header text box however you like
  • 1 x column graph showing traffic by day: you should link the date table here to show missing data
  • 1 x alluvial showing sites against time
  • 1 x matrix showing days of the week and type and average count. Turn off subtotals, and Format>General>Data format to round values
  • 1 x pie showing type against count
  • 1 x line showing diurnal change

Tidying

  • sort out the values to make sure the cctv map links properly (especially for the Murraygate and Seagate cameras)