Faysal AzizExcel and Power BI for Data Analysis and Dashboards
From data preparation to an interactive report
Prepare, model and visualise business data with Excel and Power BI in a guided three-day reporting workshop.
Why this course
This three-day workshop follows a practical data-analysis workflow from spreadsheet preparation to an interactive Power BI report. Participants practise Excel functions and pivot tables, use Power Query to clean data, build a small reporting model and create selected DAX measures.
The final exercise uses a prepared, non-confidential business dataset. Publishing and dashboard sharing are practised in a suitable training workspace where accounts, licences and permissions are available; the emphasis is on a coherent introductory project rather than mastery of every feature or production deployment.
Learning outcomes
The course teaches participants to:
- Clean and validate spreadsheet data using Excel functions and Power Query.
- Summarise data with pivot tables, charts and selected Excel data-model features.
- Build a small star-schema reporting model and distinguish calculated columns from measures.
- Write introductory DAX aggregations and selected time-intelligence calculations.
- Create usable interactive reports and distinguish reports from Power BI service dashboards.
- Explain publishing, refresh, access and sharing requirements for a training report.
- Select meaningful KPIs and communicate the limitations of the underlying data.
Prerequisites
- Basic familiarity with computers and spreadsheets.
A supported Windows computer with a current supported Excel edition, preferably Microsoft 365, and the latest 64-bit Power BI Desktop. Check Excel edition support for Power Pivot and the features used in the workshop.
Power BI service exercises require a suitable organisational training account, workspace permissions and any applicable sharing licence or capacity. Prepared demonstrations can be used where publishing access is unavailable.
- Curiosity about business data problems and analytical dashboards.
3 modules
01Day 1 — Excel Analysis and Data Preparation1 topics
Excel Foundations for Data Analysis
- Excel interface & navigation
- Ribbon, Quick Access, worksheets, navigation shortcuts
- Data entry standards
- Formatting, data types, data validation
- Key formulas & functions
- SUM, AVERAGE, COUNT, IF, AND/OR, LOOKUP functions
Advanced Excel Techniques
- Data cleanup & preparation
- Text functions, Flash Fill, Remove Duplicates
- Logical and error-handling functions
- Pivot Tables & Pivot Charts
- Grouping, slicers, filters, calculated fields
- Excel dashboards
- Designing effective visuals, layout best practices
- Introduction to Power Pivot
- Create a small Excel data model with relationships where the installed edition supports Power Pivot.
Data Import & Transformation
- Introduction to Power Query
- Connect to Excel, CSV, and other sources
- Data profiling & transformation steps
- ETL basics
- Cleaning, filtering, unpivoting, merging tables
02Day 2 — Power BI Modelling, DAX and Visualisation1 topics
Data Modeling in Power BI
- Power BI data model fundamentals
- Tables, relationships, cardinality
- Creating robust models
- Star schema concepts
- Discuss model size, appropriate granularity and efficient data types using a manageable training dataset.
- Calculated columns vs Measures
- Logic and performance
DAX (Data Analysis Expressions)
- DAX fundamentals
- Syntax and common functions
- Aggregations (SUM, AVERAGE, COUNT)
- Analytical DAX
- CALCULATE, FILTER and selected YTD/QTD measures using an appropriate date table.
- Performance considerations
Analytical Visualizations
- Chart types & best practices
- Bar, line, area, KPI cards, gauges
- Interactive elements
- Slicers, filters, drill-throughs, bookmarks
- Custom visuals
- Review selected approved custom visuals, compatibility and data-governance considerations.
03Day 3 — Reporting, Sharing and Guided Project1 topics
Dashboard Construction
- Report layout design
- Storyboarding for business goals
- Interactivity & usability
- Cross-filtering and navigation; adapt the report layout for usability.
- Mobile optimization
- Design and review a mobile report layout.
Publishing & Sharing
- Power BI Service basics
- Workspaces, semantic models, reports and service dashboards; distinguish their purposes.
- Data refresh & scheduling
- Refresh configuration, source credentials, gateway requirements where applicable, and data governance.
- Collaborative workflows
- Review authorised sharing through apps or Teams and embedding options; licences, permissions and tenant settings affect availability. Do not expose private data through public embedding.
Decision-Ready Insight Delivery
- Selecting KPIs
- Aligning visual outputs to stakeholder decisions
- Reporting automation
- Demonstrate a small repeatable Excel-to-Power BI reporting workflow, including refresh limitations.
- Final project: Integrated dashboard
- Prepared, anonymised or synthetic business dataset with documented assumptions.
- Clean, model and visualise the data; publish to a training workspace and create a service dashboard where access allows.
A programme built around your team.
Share your training goals and requirements.