Week 9: Power BI Fundamentals

The enterprise BI tool every analyst should know

Duration: 7 Days | Level: Optional Track | Free tier sufficient

R Shiny is your USP. Power BI is your bilingualism. Most Indian enterprises run on the Microsoft stack, and Power BI is the default reporting tool there. This week you learn enough Power BI - Power Query for shaping, DAX for measuring, star schemas for modelling - to walk into any enterprise interview and answer ``can you also do Power BI?'' with a confident yes.

What You'll Learn This Week

Day 1

The Power BI Stack

  • Power BI Desktop - free Windows app for authoring reports. This is where you do almost all your work.
  • Power BI Service - cloud workspace for publishing, sharing and scheduled refresh.
  • Power BI Mobile - read-only iOS / Android viewers.
Power BI is built on three engines: Power Query (shaping data), VertiPaq (in-memory columnar store), and DAX (modelling and measures). Knowing which to reach for is half the skill.

When to use Power BI vs R Shiny

Use Power BI when...Use R Shiny when...
Fast standard reports for many usersBespoke logic or interactive what-ifs
You live in Microsoft 365You need embedded statistical models
Row-level security via AAD groupsYou want full control of the UI
Day 2

Data Connections

Get Data brings up a catalogue of 100+ connectors: Excel, CSV, SQL Server, MySQL, PostgreSQL, SharePoint, REST APIs and more. Choose your mode:

  • Import - data cached in the model. Fastest queries; needs refresh schedule.
  • DirectQuery - live queries against the source on each interaction. Always current, slower visuals.
  • Live Connection - only for Analysis Services / PBI datasets.
Day 3

Power Query

The data-cleaning workhorse

Click Transform Data to open the Power Query Editor. Every action you take is recorded as a step in Applied Steps - this is the M language behind the scenes, but you rarely need to write it by hand.

The ten transformations you'll use daily

  1. Promote first row to headers
  2. Change column types
  3. Filter rows; remove blanks and duplicates
  4. Replace values; trim and clean text
  5. Split column by delimiter
  6. Pivot / Unpivot columns
  7. Merge queries (left / inner / anti join)
  8. Append queries (union)
  9. Add custom column with an M expression
  10. Group by with aggregation
Pro tip: do as much shaping as possible in Power Query, not in DAX. Power Query runs once at refresh; DAX runs on every visual interaction.
Day 4

Data Modelling

Star schemas are not optional

A star schema has one or more fact tables (orders, sales - big, narrow, additive) joined to several dimension tables (date, customer, product - small, descriptive). Power BI's engine is built for this shape. Wide ``flat'' tables are an anti-pattern.

Always create a calendar table

DateTable = ADDCOLUMNS( CALENDAR(DATE(2024,1,1), DATE(2027,12,31)), "Year", YEAR([Date]), "MonthNum", MONTH([Date]), "MonthName", FORMAT([Date], "mmm"), "Quarter", "Q" & FORMAT(QUARTER([Date]), "0"), "YearMonth", FORMAT([Date], "yyyy-mm") )

Mark it as a date table via Modeling → Mark as date table.

Day 5

Visualisations and Reports

Power BI ships with 30+ built-in visuals. The same chart-choice rules apply as in Week 5: line for trends, bar for ranks, scatter for relationships. Avoid 3D charts, dual-axis gauges and rainbow heat-maps.

Interactive features

  • Slicer - a visible filter widget on the canvas.
  • Filter pane - finer-grained filters off to the side.
  • Drill-through - click a category, jump to a detail page filtered to that value.
Day 6

DAX Fundamentals

DAX (Data Analysis Expressions) is the formula language used to define calculated columns (per-row) and measures (aggregated on demand). Almost always you want a measure.
Total Revenue = SUM ( Sales[Amount] ) YTD Revenue = TOTALYTD ( [Total Revenue], DateTable[Date] ) Revenue PY = CALCULATE ( [Total Revenue], SAMEPERIODLASTYEAR ( DateTable[Date] ) ) YoY % = DIVIDE ( [Total Revenue] - [Revenue PY], [Revenue PY] ) Top 5 Customers = CALCULATE ( [Total Revenue], TOPN ( 5, VALUES ( Customer[Name] ), [Total Revenue] ) )

CALCULATE evaluates an expression in a modified filter context. Mastering CALCULATE is the difference between a Power BI beginner and a pro.

Day 7

Project: Power BI Sales Dashboard

  1. Connect Orders, Items, Customers, Products tables.
  2. Build a star schema with relationships.
  3. Create a date table and time-intelligence measures.
  4. Build a three-page report: Overview, Customers, Products.
  5. Add slicers for region, category and date range.
  6. Publish to the Power BI Service; share the link.

A complete DAX kit is provided at /data/week9-powerbi-dax-snippets.txt.

Coming up: Week 10 - Tableau Essentials

Round out your BI toolkit. Three tools, one career.

View Detailed Curriculum