Intermediate Power BI session 1

Power BI
intermediate
Published

February 11, 2025

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

Session 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.

No feedback found for this session

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

DAX query view

  • EVALUATE()
  • 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)