Course Overview

This practical, hands-on course is designed for Excel users who want to prepare their data effectively for use in Power BI Desktop. Delegates will learn how to structure, clean, and optimise Excel data so it can be imported, modelled, and visualised seamlessly in Power BI. By the end of the day, participants will understand the key differences between Excel and Power BI data handling, and will be confident in applying best practices that reduce errors, improve efficiency, and create a solid foundation for insightful reporting.

Prerequisites

• • A good working knowledge of Excel (including formulas and tables).
    No prior experience of Power BI is required, although some familiarity with its purpose will be beneficial.
    Delegates should be comfortable working with datasets in Excel.

Course Content

• Overview

  Objectives of the day
  Common challenges when importing Excel into Power BI
  Importance of clean, structured data

• Excel Data Hygiene Essentials

  Removing blank rows, columns, and merged cells
  Clearing formatting and unused ranges
  Using “Used Range” reset techniques
  Best practices for naming sheets and ranges

• Structuring Tables for Power BI

  Converting ranges to Excel Tables (Ctrl + T)
  Naming tables meaningfully
  Benefits of structured tables: auto-expansion, metadata, and relationships

• Data Types & Formatting

  Ensuring consistent data types (text, numbers, dates)
  Avoiding mixed-type columns
  Formatting for Power BI recognition (e.g., date serials vs. text)

• Preparing Lookup & Relationship Tables

  Creating dimension tables (e.g., Products, Dates)
  Avoiding duplication and redundancy
  Using Excel formulas to normalize data (e.g., UNIQUE, SORT, TEXTJOIN)

• Importing into Power BI Desktop

  Import methods: Excel workbook vs. table selection
  Navigating the Navigator pane
  Verifying data model structure post-import

• Manipulating Data in Power Query Editor

  Splitting Columns intelligently
  Grouping and Totalling
  Changing Data types
  Replacing and removing errors and rows
  Managing Applied Steps

• Relationships in the Model

  Examining Relationships in Model View
  Managing and creating Relationships
  Using RELATED() columns in Calculations
  Navigating the Navigator pane
  Verifying data model structure post-import

• Visualising Excel Data

  Adding Visuals to Pages
  Visuals using multiple related Tables
  Dashboarding essential Measures and Data
  Formatting Visuals and the Report
  Filtering vs Slicing

• Wrap-Up & Q&A

  Recap of key takeaways
  Resources for further learning
  Open Q&A and feedback