(0112 601 803 /0766 560 002

Web: www.aict.lk

Reg No: W/P 81396

Advanced Data Analytics with Power BI Desktop

Module 1: Introduction to Business Intelligence (4 Hours)

Goal: Understand the ecosystem and interface.

· The Data Revolution: What is BI and why is it better than Excel?

· Power BI Ecosystem: Desktop vs. Service vs. Mobile.

· Installation & Interface: Tour of the Report, Data, and Model views.

· Connecting to Data:

o Excel Workbooks & CSV files.

o Web Data (Scraping tables from a website).

o Folder connection (Combining multiple monthly files automatically).

Module 2: Data Transformation with Power Query (8 Hours)

Goal: The "Kitchen" of Power BI – Cleaning messy data.

· The Query Editor: Navigating the interface.

· Basic Cleaning: Removing rows, promoting headers, changing data types.

· Text & Number Transformations: Splitting columns, trimming, extracting text.

· Advanced Shaping:

o Unpivoting: Turning wide data (months as columns) into tall data (database format).

o Merging Queries: The "VLOOKUP" of Power BI.

o Appending Queries: Stacking data tables (Jan + Feb + Mar).

· Conditional Columns: Creating logic (IF/THEN) in Power Query.

Module 3: Data Modeling & Relationships (6 Hours)

Goal: The "Heart" of the system – Building a robust data structure.

· Data Modeling 101: Fact Tables vs. Dimension Tables.

· Star Schema: Why it is the gold standard for performance.

· Managing Relationships:

o Cardinality (One-to-One, One-to-Many).

o Cross-filter direction (Single vs. Both).

o Active vs. Inactive relationships.

· Hierarchies: Creating Drill-down paths (Year > Quarter > Month).

Module 4: DAX (Data Analysis Expressions) Mastery (10 Hours)

Goal: The "Brain" – Writing formulas for complex calculations.

· DAX Basics: Calculated Columns vs. Measures (When to use which?).

· Aggregation Functions: SUM, AVERAGE, COUNT, DISTINCTCOUNT.

· Logical Functions: IF, SWITCH, AND, OR.

· The Most Important Function: Understanding CALCULATE and filter context.

· Table Functions: FILTER, ALL, ALLEXCEPT.

· Time Intelligence (Crucial for Business):

o TOTALYTD (Year-to-Date).

o SAMEPERIODLASTYEAR (Growth comparisons).

o DATEADD (Rolling averages).

Module 5: Data Visualization & Storytelling (8 Hours)

Goal: The "Face" – Creating stunning, interactive reports.

· Standard Visuals: Bar, Line, Pie, Map, Scatter Plots.

· Formatting: Colors, Titles, Backgrounds, and conditional formatting.

· Interactivity:

o Slicers & Sync Slicers (Filtering across pages).

o Drill-through filters (Moving from summary to details).

o Tooltips (Hovering to see more data).

· Advanced Features:

o Bookmarks & Selection Pane (Creating toggle buttons).

o Q&A Visual (AI-based questioning).

o Mobile Layout design.

Module 6: Publishing & Collaboration (2 Hours)

Goal: Sharing the work.

· Power BI Service: Publishing reports to the cloud.

· Dashboards vs. Reports: Understanding the difference.

· Exporting: Export to PDF/PowerPoint.

· Data Refresh: Setting up scheduled refreshes (Gateway basics).

Module 7: Capstone Project (2 Hours + Homework)

· Real-world Scenario: Students are given a raw dataset (e.g., "Superstore Sales" or a "Sri Lankan Retail Data").

· Requirement: They must Clean, Model, Calculate (DAX), and Visualize the data to answer 5 specific business questions (e.g., "Which region had the highest profit growth compared to last year?").