Applied Data Analytics, Visualization, and Advanced Dashboarding with Power BI  Overview

This course develops the skills needed to transform raw business data into reliable, interactive Power BI reports and dashboards. Designed for participants transitioning from Microsoft Excel or Google Sheets, it covers the complete analytics workflow: defining business questions, connecting to data, preparing and transforming datasets, building data models, creating DAX calculations, designing visualizations, and publishing and maintaining reports.

Participants progress from core Power BI concepts to advanced dashboarding, including normalization and selective denormalization, star-schema modeling, time intelligence, interactive reporting, performance optimization, row-level security, and deployment governance. The course also introduces SQL for retrieving and preparing data and communicating findings that support business decisions.

 

Duration 4 Days – 28 hrs.

 

Objectives

• Translate business requirements into analytical questions, meaningful metrics, and reporting requirements.
• Connect Power BI to Google Sheets, Excel, CSV files, folders, web sources, and relational databases.
• Identify data-quality issues and determine whether changes belong in the source, Power Query, the data model, or DAX.
• Clean and reshape data using repeatable Power Query transformations.
• Apply normalization and selective denormalization while preserving data grain and accurate totals.
• Build star-schema models with appropriate keys, relationships, date tables, and filter directions.
• Create reusable DAX measures using aggregation, filter context, iterators, and time intelligence.
• Design accessible, interactive reports and dashboards that communicate clear business insights.
• Apply advanced visualization features, row-level security, and performance optimization techniques.
• Publish and manage Power BI content with appropriate refresh, access, ownership, and governance controls.
• Write SQL queries to filter, aggregate, and combine data for Power BI.
• Communicate findings, business implications, limitations, and recommended actions.

 

Target Audience 

• Business analysts and data analysts.
• Business intelligence and reporting professionals.
• Microsoft Excel and Google Sheets users transitioning to Power BI.
• Finance, sales, marketing, human resources, and operations personnel responsible for reporting.
• Managers, supervisors, and team leaders who develop or interpret business dashboards.
• IT professionals and report developers supporting data preparation, reporting, and deployment.

 

Prerequisites

• Basic proficiency in formulas, filters, sorting, and charts in Microsoft Excel or Google Sheets.
• No previous DAX or SQL experience required.
• A Windows laptop with Power BI Desktop installed.
• Access to relevant Google Sheets, Excel files, and training database sources.
• Appropriate Power BI Service access and organizational permissions for publishing and sharing activities.
• An anonymized business dataset, if available, for applying the course concepts.

 

Course Outline

 Day 1: Power BI Foundations, Data Preparation, and Modeling

Module 1: Power BI Ecosystem Overview

• Power BI Desktop, Power BI Service, and mobile consumption.
• Workspaces, apps, reports, dashboards, and semantic models.
• The analytics workflow from business question to published report.
• Transitioning from cell-based spreadsheets to reusable tables, relationships, and measures.
• Licensing and sharing concepts.

 Module 2: Connecting to Data and Preparing Spreadsheet Sources

• Connecting to Google Sheets, Excel, CSV files, folders, SharePoint/OneDrive, web sources, SQL Server, and Power BI semantic models.
• Google Sheets access, authentication, sheet URLs, Navigator, and Transform Data versus Load.
• Excel worksheets, formatted tables, named objects, and stable file locations.
• Spreadsheet readiness: unique headers, consistent data types, stable IDs, and separation of raw data from summaries.
• Source corrections versus analytical transformations in Power Query.
• Import, DirectQuery, live connections, and composite-model concepts.
• Data privacy, credentials, refresh ownership, gateways, and query folding fundamentals.

 Module 3: Power Query—Data Profiling, Cleaning, and Reshaping

• Power Query interface, Applied Steps, Query Settings, and profiling tools.
• Identifying nulls, errors, duplicates, inconsistent categories, and incorrect data types.
• Promoting headers, removing unnecessary rows, trimming text, replacing values, and handling errors.
• Splitting and merging columns, filling values, pivoting, unpivoting, grouping, merging, and appending.
• Conditional columns, custom columns, staging queries, references, duplicates, and parameters.
• Normalizing repeating columns and multi-value cells into analysis-ready tables.
• Defining data grain, identifying entities, and creating fact, dimension, and bridge tables.
• Selective denormalization and the risks of duplicated rows and mixed grains.
• Designing refresh-safe transformations and reconciling row counts and totals.

Module 4: Data Modeling Basics

• Fact tables, dimension tables, business keys, and surrogate keys.
• Star-schema principles and table grain.
• Relationship cardinality, active and inactive relationships, and cross-filter direction.
• Many-to-many relationship concepts and unmatched keys.
• Date dimensions and governed time intelligence.
• Model organization, field descriptions, formatting, and sort-by columns.
• Validating model relationships and source totals.

 Module 5: Introduction to DAX

• Measures, calculated columns, and calculated tables.
• SUM, AVERAGE, MIN, MAX, COUNTROWS, and DISTINCTCOUNT.
• DIVIDE and COALESCE.
• Row context and filter context.
• CALCULATE and modified filter context.
• Measure branching, naming conventions, formatting, and calculation validation.

 

 Day 2: Data Storytelling, Visualization, and Communication

Module 6: Data Storytelling Principles

• Defining the audience, decision, business question, metric, and required action.
• Structuring a narrative around context, findings, implications, and recommendations.
• Descriptive, diagnostic, predictive, and prescriptive questions.
• Establishing one main message per visual and a clear purpose for each report page.

 Module 7: Choosing the Right Visual

• Visual selection for comparison, trends, composition, distribution, and relationships.
• Bar charts, column charts, line charts, scatter plots, tables, matrices, and KPI cards.
• Appropriate use of stacked visuals and approved custom visuals.
• Consistent scales, clear axes, and meaningful comparisons.
• Avoiding misleading visuals and unnecessary decoration.

 Module 8: Visual Design and Accessibility

• Visual hierarchy, alignment, whitespace, and grid-based layouts.
• Titles, subtitles, units, labels, and number formatting.
• Sequential, diverging, and categorical color schemes.
• Accessible contrast, alternative text, tab order, and descriptive titles.
• Making the report topic, filter context, and main takeaway immediately clear.

 Module 9: Interactivity as a Storytelling Tool

• Slicers and filter panes aligned with business decisions.
• Drill-through and cross-highlighting.
• Bookmarks, buttons, and page navigation.
• Report-page tooltips.
• Reset filters and visible filter states.

 Module 10: Communicating Findings

• Executive summaries covering KPI status, trends, drivers, risks, and opportunities.
• Presenting insights with supporting evidence.
• Separating observed results from analytical interpretation.
• Communicating confidence, limitations, and recommended actions.
• Documenting refresh dates, metric definitions, and data-quality limitations.

 

 Day 3: Advanced Dashboarding, Security, and Deployment

Module 11: Advanced DAX

• Filter modifiers: REMOVEFILTERS, ALL, ALLEXCEPT, and ALLSELECTED.
• Time intelligence using a marked calendar table.
• Year-to-date, prior-year, rolling-period, and fiscal-calendar calculations.
• Iterators, including SUMX and AVERAGEX.
• Variables using VAR and RETURN.
• SELECTEDVALUE, dynamic titles, and disconnected parameter tables.
• Field parameters and an introduction to virtual relationships using TREATAS.

 Module 12: Advanced Modeling and Row-Level Security

• Role-playing date dimensions and inactive relationships.
• USERELATIONSHIP.
• Bridge tables and many-to-many relationships.
• Allocation scenarios and prevention of double counting.
• Composite-model concepts and source-group implications.
• Static and dynamic row-level security.
• Testing roles and documenting security keys.

 Module 13: Performance Optimization

• Using Performance Analyzer to identify and investigate bottlenecks.
• Reducing model size by removing unnecessary rows and columns.
• Selecting appropriate data types and managing high-cardinality fields.
• Avoiding unnecessary calculated columns.
• Managing visual density and costly interactions.
• Preserving query folding where supported.
• Using star schemas and appropriate aggregation tables.

 Module 14: Advanced Visualization Techniques

• Field parameters and what-if parameters.
• Small multiples and sparklines.
• Custom tooltips and drill-through detail pages.
• Designing coordinated, multi-page reports.
• Custom visual selection, certification, accessibility, data handling, maintenance, and export considerations.

Module 15: Deployment and Governance

• Publishing reports and organizing workspaces and apps.
• Permissions, ownership, endorsement, and deployment concepts.
• Refresh configuration, credentials, cloud connections, and on-premises gateways.
• Validating row-level security in Power BI Service.
• Lineage, sensitivity labels, and source documentation.
• Versioning, change logs, release readiness, rollback planning, and support ownership.

 

 Day 4: SQL for Power BI and Integrated Business Reporting

Module 16: SQL Fundamentals

• SELECT, FROM, WHERE, ORDER BY, aliases, and DISTINCT.
• TOP and LIMIT concepts.
• NULL behavior and data types.
• AND, OR, IN, BETWEEN, and LIKE.
• CASE expressions and appropriate filtering.

Module 17: Aggregation and Joins

• COUNT, SUM, AVG, MIN, and MAX.
• GROUP BY and HAVING.
• INNER, LEFT, and RIGHT joins.
• FULL OUTER JOIN concepts.
• Join cardinality, duplicate keys, row multiplication, and total reconciliation.
• Differences among SQL joins, Power Query merges, and model relationships.

 Module 18: Writing Queries for Power BI

• Native SQL queries, database views, and Power Query-generated SQL.
• Query folding and source-side processing.
• Common Table Expressions and reusable source logic.
• Parameters and least-privilege database access.
• Protecting credentials and confidential information.
• Locating business logic in source views, Power Query, model measures, or visuals.

 Module 19: Integrated Analytics Solution and Business Presentation

• Combining spreadsheet data and normalized SQL sources.
• Integrating data preparation, modeling, DAX calculations, visualization, and publishing.
• Documenting business questions, source inventories, transformation decisions, and table grain.
• Maintaining a measure dictionary and reconciling source totals.
• Structuring executive summary, analysis, and detail report pages.
• Documenting refresh, security, ownership, and data limitations.
• Communicating three key findings, their business implications, and recommended actions.