Power BI

Power BI is where the cleaned data from your previous topics becomes something a business can use: an interactive report a manager can filter, drill into, and refresh every morning. It is the most-wanted dashboard tool in current analyst job ads, and it rewards exactly the skills you already built — Excel logic, SQL-style joins and aggregations carry over almost one-to-one.

The mental shift this topic demands is the big one of the whole path: you stop analyzing cells and start modeling relationships between tables, then you write measures (DAX) that stay correct no matter how the user slices the report. Master that, and Power BI feels less like charting software and more like building a small analytics product.

Basics, Excel, SQL and Data Cleaning — Power Query and DAX assume you understand joins, aggregation and clean tables. Statistics vocabulary (percentiles, outliers) helps when choosing visuals.

Power BI has two halves. Power BI Desktop (a free Windows app) is where you build — connect data, transform it, model it, design visuals. Power BI Service (cloud) is where you publish a finished report so colleagues view it, and where you schedule refresh so the numbers stay current without you re-clicking anything.

Your workflow is always the same four rooms, left to right: Get Data → Transform (Power Query) → Model (relationships) → Report. Later you add publish & refresh. If Excel was your scratchpad and SQL your query engine, Power BI is the production display where the business actually watches the scoreboard.

Power Query is your data-cleaning topic made reusable: every click you make — rename, remove columns, split, merge, filter, change type — becomes a step in a list that replays on every refresh. Because it is the same engine inside Excel and Power BI, everything you learned in Data Cleaning applies verbatim.

• Connect to files, folders, databases and web sources via Get Data
• Append = stack tables (your regional exports); Merge = join tables (your SQL JOIN here)
• Do the last step for most columns: set the correct type. The wrong type at the end silently breaks your model.

Good hygiene: never write a hack transform inside a visual — every permanent change belongs in Power Query steps, which are the versioned, repeatable history of your cleaning.

The most important habit in Power BI: a flat mega-table is a trap. Instead, split your data into a star schema:

• Fact table — the measurement events (orders: row = one order, with numeric facts and key columns)
• Dimension tables — the descriptive context (customers, products, dates/calendar) that you slice by

One simple pattern: orders (fact) sits in the middle; customers, products and a Date calendar join to it. This avoids duplicated description (customer name repeated on every order row), shrinks file size, and — crucially — makes DAX aggregations fast and predictable.

Build a proper Date/Calendar table (a table of dates spanning your data, each with year/month/quarter) and link it to your fact's date column. Almost every professional measure needs it.

A relationship connects two tables on a key column and defines how filtering flows from one to the other. Get three things right and you can forget about it:

• Cardinality — most are one-to-many: one product (the "one" side) → many order lines (the "many" side)
• Filter direction — filters flow from the dimension (product) to the fact (orders) via the arrow; that is what lets you "slicer, then see orders"
• Single direction for 95% of cases — bidirectional arrows exist but create ambiguity; avoid unless you truly understand why

Test your model like this: put product category and total sales on a visual, apply a filter, and confirm the result drops the way the business expects. If it does not, the relationship — not the DAX — is usually guilty.

Here is the mental model that separates a Power BI user from an analyst who designs with it. A calculated column is baked at load time, once per row (like adding a column in Excel). A measure is evaluated at query time for whatever slice the report currently shows.

Total Revenue   = SUM( orders[amount] )

Revenue per Order = DIVIDE( [Total Revenue], COUNTROWS( orders ) )

Because a measure reacts to the current filter context — the slicer, the row, the page — the same measure is correct at every level: total, per month, per region, per product. That is the core Power BI advantage and the thing interviewers probe. Rule of thumb: write measures for numbers and ratios; use calculated columns only for static row-level attributes (e.g. "High / Medium / Low" buckets).

A small set of patterns covers nearly all analyst asks. All are measures over a star model:

-- Percent of total
Revenue % of Total = DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL( products )))

-- vs previous year, with time intelligence
Revenue vs PY     = [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR( dates[Date] ))

-- 12-month rolling total
Revenue rolling 12M = CALCULATE([Total Revenue],
                        DATESINPERIOD( dates[Date], MAX( dates[Date] ), -12, MONTH ))

-- Top 5 products by revenue
Top 5 Revenue = CALCULATE([Total Revenue],
                 TOPN( 5, products, [Total Revenue] ))

• CALCULATE is the one function to master — it re-writes the current filter context before evaluating its measure
• ALL() removes filters (percent-of-total); ALLSELECTED() respects the page slicers
• Time intelligence needs the Date table linked to your fact

Write each pattern once, verify against a hand-calculated number, and you will have a personal measure library you reuse in every report.

Every visual on a page should answer one question — if it does not survive the "so what?" test, it is decoration. The practical menu:

• KPI card — single headline number (+ vs target or prior period)
• Line/area — trends over time; group by month/quarter
• Bar/column — comparison/ranking (top regions, categories); horizontal bars for long labels
• Pie/donut — only for a few slices that sum to a meaningful whole; prefer bars otherwise
• Matrix/table — exact numbers and drill-downs (year → quarter → month)
• Slicers — the filters your audience will actually use

Interactivity: slicers, drill-down on matrices (year → month), drill-through pages (region page from a detail page), and clearly labeled tooltips make a report feel alive without overwhelming the main page. Formatting rule: one accent color for the "star" number, neutral grays for context, no rainbow.

A dashboard that works in a meeting reads top-to-bottom, left-to-right, one decision per page:

1. Title + as-of date up top (the audience must know how fresh it is)
2. Row of KPI cards — the scoreboard first
3. Then the trend (what happened over time)
4. Then breakdowns (by region, product, channel) and the cross-filter slicers

Use a 12-column grid, alignment guides, consistent fonts and spacing. Every slicer and visual gets a label. Then the professional move: look at it on a small screen — most consumers will — and in black-and-white print, to catch low-contrast crimes.

The report only matters if people can see current numbers. Publish to the Service, then set: scheduled refresh (daily/ hourly from your data sources — the point of your reproducible cleaning; it now runs itself) and permissions. Two security layers are worth knowing:

• App workspaces + sharing — who can view/edit the report
• Row-Level Security (RLS) — same report, filtered per role (a regional manager sees only their region). Implement with a small role table + DAX filtering rules, enabled in the Service

Finish with a short data dictionary / refresh note tab inside the report file (or a linked doc): sources, refresh schedule, and definitions of the key measures. Self-serve analytics means the consumer can also understand what they are looking at.

Use a superstore-style CSV (you can reuse your cleaned data-cleaning output).

1. Import it, then split it into orders (fact) + customers, products and a Date table (dimensions) in Power Query.
2. Create the relationships and verify filter flow by slicing total revenue by category.
3. Write the DAX patterns above and check each against a hand-calculated PivotTable number.
4. Build a two-page report: a monthly KPI page (cards, trend, category bar, region matrix, slicers) and a drill through page for one region.
5. Publish a copy to the Service and document the refresh plan.

Analyst job ads ask for Power BI more than any other dashboard tool, and the interviews typically include a "build me a report" task. What they evaluate is not visual flair — it is whether you model the data properly, write measures that hold up under any filter, and keep the business logic (the as-of date, the definitions) visible. In daily work, Power BI is where your SQL and cleaning skills turn into the thing managers actually look at on Monday mornings; a good model is the difference between "this dashboard refreshed itself" and "why does this number disagree with my Excel?"

Business problem: The regional managers need one self-serve monthly review they can filter to their region — without emailing you for a new screenshot every week.

Dataset: a 1,000+ row sales export with order date, region, category, product, salesperson, sales amount (or reuse your cleaned data-cleaning export).

Objective: a published, refreshable, filtered-by-region monthly business review.