BI for Beginners (service version) Session 1

Power BI
beginner
Published

September 12, 2024

Session materials

No feedback found for this session

Session outline

  • practical first
    • loading data
    • making visualizations
    • manipulating data
  • then some explanation and examples
    • what is it for?
    • how does it work?

Two words of warning…

  1. This course uses Power BI Service. Most of the skills here are transferable to other versions of Power BI, but you’ll need Service to follow the examples
  2. Licencing and publication can be complicated in Power BI. We don’t discuss that much here, but it’s important to understand the implications if you’re planning to use it in practice. The KIND Network is a good resource for those kinds of questions.

Hospital occupancy data

Task
  1. download, and open the sample s01_data.xlsx workbook in Excel
  2. have a look at the two sheets, called occupancy_sh and wards_sh
  3. each contains a table, which are named occupancy and wards
  4. close the Excel file

Open Power BI service

Task
  1. Navigate to https://app.powerbi.com/
  2. Select New Report

Loading data

Task
  1. select Excel (preview)
    select Excel (preview)
  2. select Upload file
  3. now drag your Excel workbook into the Power BI window
    drag your Excel workbook into the Power BI window

Hospital occupancy data

Task
  1. we can either load the worksheets or the tables from the Excel file
    1. if you’re planning to work with data from Excel, we’d strongly advise you to work with tables rather than worksheets
  2. select the two tables in the preview and select Create
    select the two tables in the preview and select Create

The Report View

Report view
  • this is where we’ll build our dashboard
  • take a minute to look round - particularly noting the three panes (Filters, Visualizations, and Data) on the right of the screen
  • you should also save your report at this point
    Save your report

A note on data loading

  • data loading is one-way
    • what we do in Power BI won’t change the underlying files
    • but if our files change, our dashboard will change
      • we’ll usually need to refresh our data to see that

A first visual

  • for now, let’s use this data to build a simple visualization
Task
  1. make sure you select Edit from the menu
    select Edit
  2. Select Line chart from the visualizations pane
    Select Line chart from the visualizations pane
  3. Enlarge the visualisation to fill the page by dragging the corners of the new line chart to fill the report area
  4. Add some data:
    1. Drag the OccupiedBeds column from the occupancy table to the Y-axis field
    2. Drag the date column from the occupancy table to the X-axis field
      date in x axis, occupiedbeds in the y axis
  • you should have a nice, craggy, time series graph of bed occupancy
    Bed occupancy time series
  • don’t panic if you have a straight-line graph
    Straight line graph
  • Power BI will usually aggregate your data. This straight line graph comes from Power BI adding all the values for the years 2022 and 2023 together, and plotting a graph between those two points
  • you control aggregation from the drop-down menu on the data in your field
    drop-down menu on the date data
  • we can do similar aggregations on our other data too. Swap Sum for Average in your OccupiedBeds field drop-down menu on the occupied beds data

Making our chart more useful

Task
  1. Try dragging the WardName column to the Legend field

or to the Small multiples field:

Formatting

  • formatting options live in the paintbrush tab above the grid of visualisations
  • there are lots of options there
  • a good homework project would be to experiment with re-styling your graph

Changing our chart type

  • this is easy: just make sure your chart is selected (look for the tiny grey corners graph corners), and click a new graph icon (liked stacked columns new graph icon)
  • that will change the type of graph you’re building - in this case, to a stacked column chart
    stacked column chart

What’s happening behind the scenes?

  • to to your workspace

  • you should see a graph for your dashboard

  • now follow the ... menu from the Semantic model to Open data model Open data model

The data model

  • the data model gives you an overview of your data Data model
  • we can also modify aspects of the data from here (data types, relationships, …)

Calculated columns

  • Calculated columns are how we describe columns calculated from other values
  • Here, we could calculate something simple for each ward - how about the number of non-closed beds available?
Task
  1. select New column from the ribbon
    select New column from the ribbon
  2. now we write a formula in the formula bar: AvailableBeds = [BedComp] - [ClosedBed]
    add a formula to the formula bar
  3. AvailableBeds = our new column name
  4. [BedComp] and [ClosedBed] are the existing column names that we’re subtracting
  5. now return to your report. You should see your new calculated column available for use
  • Congratulations! You’ve just written your first DAX formula.

What is DAX?

DAX (Data Analysis eXpressions) is the formula language used in Power BI, Power Pivot, and across various MS SQL tools…

  • similar to Excel formulas
    • functions have similar names and similar behaviours
    • DAX is more ‘fussy’ than Excel
  • we’ll talk about DAX more in session 4

What is Power BI for?

Power BI has attractive features

  • Interactive
  • Dynamic
  • Presenting data from many sources
  • Scalable
  • Separates data from analysis/presentation

Homework tasks

  1. Try using the filters pane to play with your dashboard. How do filters change your visual?
  2. Could you use the data from this session to find out how often a ward is overcrowded?
  3. Can you plot the AvailableBeds column?