Introduction to Power BI – DEC Report Video 6

Intro to Power BI #6 – 12 mins 17 secs

Video 6 – Data Modelling / Summary Tables

• Data Modelling – definition and why this is so powerful
• Using summary tables
• Import summary table – College List
• Transform College List – use first row as headers, Close and Apply
• Return to Model View – College List auto-connected to DEC Report
• How to connect summary tables if necessary
• Page 5 – Lets investigate how successful ( or otherwise) the respective Delivery Methods are
• Rename Page 5 to Delivery Methods Analysis
• Visual – Clustered Column Chart
  • X-axis – college ( use the Summary Table field )
  • Y-axis – Completion Rate – change to Average
  • Legend – Delivery Method
• Format visual
• Creating summary tables – let’s say we wanted a breakdown of students and Completion Rates in the 3 respective courses
• Return to your Excel spreadsheet
• Create a new sheet named Course Name List
• In the DEC Report data, copy the entire column – Course Name
• TOP TIP: – to save constantly scrolling up and down a large dataset in Excel
  • Put your cursor in the top cell
  • Hold CTRL + SHIFT + down arrow
  • This will take you right to the bottom of the dataset
  • This is also a great way to check if you have any missing data – Excel will ‘stop’ at the first blank cell
• Copy the whole Course Name list and paste into your new Excel sheet
• Leave the data ‘selected’.
• Go to Data (in the ribbon) and select Remove Duplicates
• You now have a list of all the unique values in the Course Name field, with no way of mis-typing / spelling / incorrect capitalisation
• Close your Excel file and connect your new Course Name table to your Power BI
• Transform to remove ‘Column 1’
• Close and Apply and check Model View to ensure your new table is connected