Power BI: Build Your First Dashboard

Book 23 of 50 — AstolixGen Learning Series For researcher and publication students

---

About This Book

Most researchers collect data and then drown in it. You run the survey, download the experiment logs, export the sensor readings — and now you have a spreadsheet with hundreds or thousands of rows that no supervisor wants to scroll through. What your thesis committee, your journal reviewers, and your conference audience actually want is a clear picture: one page that shows what you found, lets them ask questions of the data, and updates itself when you add new responses.

That is what Power BI does. It is Microsoft's business intelligence platform, but do not let the word "business" fool you — universities, NGOs, and research teams use it every day to explore data, build interactive reports, and publish professional dashboards. With Power BI you can connect to an Excel file or a CSV, clean it up, join tables together, calculate measures like averages and growth rates, and build a report full of charts, maps, and slicers that your readers can interact with. You can then publish that report to the web and share a live link in your thesis appendix or your presentation.

This book takes you from zero to a complete published dashboard in twelve detailed chapters. You do not need programming experience. You do not need a paid license to start. All you need is a Windows computer and the free Power BI Desktop application. Along the way we will work with one running example — a household survey dataset, exactly the kind of data an MS or PhD student collects — so every skill you learn is immediately tied to a real research scenario.

By the end of this book you will have built, cleaned, modeled, measured, visualized, and published a complete research dashboard — the kind of artifact that strengthens a thesis, impresses reviewers, and communicates results far better than a static table ever could.

Prerequisites: You should be comfortable with spreadsheets (sorting, filtering, basic formulas like SUM and AVERAGE) and you should have a Windows PC (or Windows access via a lab machine or virtual machine) for Power BI Desktop. No programming, statistics, or database experience is assumed — every DAX formula and every Power Query step is introduced from zero, and the one running example is reused across all twelve chapters so nothing is ever unfamiliar for long.

A note on versions: Power BI Desktop updates monthly, so buttons occasionally move and screenshots in any book drift out of date. The concepts in this book — queries, relationships, filter context, measures — have been stable for years and will remain so. If a click-path does not match your screen exactly, search the ribbon for the feature's name; it is there under a nearby tab. When in doubt, Microsoft Learn's Power BI documentation (see References) is the authoritative, always-current companion to this book.

Learning objectives: By the end of this book, you will be able to: - Explain what Power BI is and choose the right component (Desktop, Service, Mobile) and license for a research project. - Install and navigate Power BI Desktop, including its Report, Data, and Model views. - Connect to Excel, CSV, web, and database sources, and understand the difference between Import and DirectQuery storage modes. - Use Power Query Editor to clean real-world messy data: remove errors, fix data types, split and merge columns, unpivot survey tables, and build a repeatable refreshable pipeline. - Design a simple data model with correct relationships and a star schema, avoiding the double-counting and ambiguity problems that ruin analyses. - Build the essential visuals — bar and column charts, line charts, cards, tables, matrices, maps, and KPIs — and choose the right chart for the question you are answering. - Control interactivity with filters, slicers, drillthrough, and cross-filtering, and validate that every interaction shows correct numbers. - Write your first DAX measures — sums, averages, counts, percentages, and time-intelligence basics — and understand the difference between a measure and a calculated column. - Apply professional formatting and design principles so your report looks like a publication-quality figure, not a spreadsheet screenshot. - Publish a report to the Power BI Service, manage datasets and scheduled refresh, and share it securely with collaborators or supervisors. - Present research results as a dashboard: selecting findings, adding narrative text, and making the dashboard defensible in a thesis defense. - Complete an end-to-end capstone: from a raw CSV file to a published, shareable dashboard.

How to use this book: Chapters 1–3 get you installed and connected (one sitting). Chapters 4–5 build your data foundation — do not rush these; every later chapter assumes them. Chapters 6–9 are the creative core: build alongside the text with Power BI open. Chapters 10–11 take your work public and defensible. Chapter 12 is the full rehearsal. Each chapter ends with a "For your research" box tying the skill to thesis work and a pitfall list drawn from real beginner mistakes — read the pitfalls even if you skim the chapter; they are the condensed wisdom of this book.

---

Chapter 1: What Is Power BI? Desktop, Service, Mobile, and Licensing

1.1 The big picture: what business intelligence means for research

Business intelligence (BI) is a set of tools and methods that turn raw data into decisions. In companies, that means sales dashboards and profit reports. In research, the "decision" is different: your readers decide whether your findings are trustworthy, your committee decides whether your defense passes, and other researchers decide whether to build on your work. The mechanism, though, is the same — you take raw observations, shape them into evidence, and present them so a human brain can grasp the pattern in seconds instead of hours.

Before BI tools, the typical research workflow was: collect data in Excel, wrestle with formulas, paste charts into Word, and produce a static document. Every time the data changed — new survey responses, a corrected coding — you repeated the whole painful cycle by hand. Power BI replaces that cycle with a refreshable pipeline: you define the cleaning steps and calculations once, and every new batch of data flows through them automatically. Your report updates itself. For a student collecting survey responses over several months, this is transformative. Your Chapter 4 results section can stay current until the day you submit.

1.2 The three main components

Power BI Desktop is a free Windows application where you do almost all the authoring work. This is where you connect to data, clean it in Power Query, build the data model, write DAX calculations, and design report pages. Think of Desktop as your laboratory. You will spend Chapters 2 through 9 of this book inside it. One limitation to know upfront: Power BI Desktop runs only on Windows. If you use a Mac or Linux machine, you will need a Windows virtual machine, remote desktop to a lab PC, or the browser-based authoring available in the Power BI Service.

Power BI Service (also called the Power BI portal) is the cloud part, at app.powerbi.com. After you build a report in Desktop, you publish it to the Service. There it lives in a workspace, where it can be refreshed on a schedule, viewed in a browser by anyone you share it with, and pinned into dashboards. The Service is also where collaboration happens: comments, subscriptions (scheduled emails of report screenshots), alerts on data changes, and sharing with specific people or groups. For research teams, the Service is the shared reading room: you keep the data safe and let your supervisor, co-authors, or committee view the live report.

Power BI Mobile is the phone and tablet app (iOS and Android) that lets you view reports and dashboards published to the Service. You will not design reports on it, but it matters for research: imagine walking into a field site or a stakeholder meeting and pulling up your live dashboard on your phone. It is also genuinely useful for a defense rehearsal — one less laptop cable to fight.

There are also a few smaller pieces you will hear about. Power BI Report Builder creates old-style paginated reports (pixel-perfect documents for printing — the kind of formal report an institution might require). Power BI Report Server is an on-premises server for organizations that cannot put data in the cloud. As a student, you can ignore both for now; Desktop plus Service covers everything in this book.

1.3 Licensing: what is free and what costs money

Licensing is the part of Power BI that confuses everyone, so let us make it plain.

• Power BI Desktop: free, always. Download it from the Microsoft Store or Microsoft's download center. No license, no expiry, no feature restrictions that matter for learning. Every chapter of this book can be completed with Desktop alone.
• Power BI Service (free tier): Signing in requires a work or school email — personal Gmail or Yahoo accounts are not accepted for the Service (Microsoft requires an organizational email). If your university gives you a student email on Office 365, that usually works. With the free license you get My Workspace, where you can publish reports, but you cannot share them with other people — sharing requires Pro or Premium. You can, however, publish to the web publicly (never do this with real research data) and export reports.
• Power BI Pro: a per-user monthly license. This unlocks sharing, collaboration, and most Service features. Universities frequently provide Pro through their Microsoft 365 education plan — check with your IT department before paying anything. If your supervisor has Pro, they can share workspaces with you, but you also need Pro to use shared content in most configurations.
• Power BI Premium Per User (PPU) and Premium capacity: advanced features (larger models, AI visuals, paginated reports, dataflows). You do not need these for this book or for a first research dashboard.
• Fabric: Microsoft's newer analytics platform (Microsoft Fabric) includes Power BI as its visualization layer. Fabric trial capacities give you Premium-like features for 60 days. Worth knowing about, but not required here.

Bottom line for students: Install free Desktop. If your university email unlocks a free or Pro Service account, use it. Do not buy a license out of your own pocket for a first project — confirm with your institution first.

1.4 How Power BI compares to the alternatives

You might reasonably ask: why not just use Excel charts, or Python with matplotlib, or SPSS? Here is the honest comparison.

Excel is wonderful for small, static analysis, but its charts do not refresh through a defined pipeline, it struggles past a few hundred thousand rows, and sharing "the dashboard" usually means emailing a file. Python is powerful and fully programmable, but it demands coding skill, and turning a matplotlib script into an interactive report your non-technical supervisor can explore requires extra frameworks. SPSS/Stata/R serve statistical analysis beautifully, but their interactive reporting capabilities are limited. Power BI's strength is the combination: a point-and-click data-cleaning pipeline, a real analytical engine (DAX), genuinely interactive visuals, and one-click publishing to the web — in a single free tool. Its weakness: it is weaker than Python or R for custom statistics and machine learning, and it is Windows-centric.

A sensible research stack often combines them: clean and analyze in Python or SPSS if you must, then present in Power BI — or do the whole descriptive-statistics layer in Power BI and reserve the statistics software for hypothesis testing.

1.5 A note on Power BI's analytical engine

Under the hood, Power BI's calculations run on the same engine that powers SQL Server Analysis Services — an in-memory columnar database. You do not need to understand the internals, but the practical consequence matters: Power BI is extremely fast at aggregating millions of rows. Filtering, summing, and averaging across a million-row dataset happens in fractions of a second. That is why a dashboard feels instant even with serious data. It also means Power BI is designed around a specific mental model — tables, relationships, and measures — which Chapters 5 and 8 will teach you carefully. Half of all beginner confusion in Power BI comes from fighting this model instead of working with it.

1.6 Power BI inside Microsoft Fabric: what students should know

In recent years Microsoft has folded Power BI into a larger platform called Microsoft Fabric — a unified analytics environment that also includes data engineering (Spark notebooks), data warehousing, real-time streaming, and AI services, all sharing a common storage layer called OneLake. As a student you do not need to learn Fabric to complete this book, but you should understand where Power BI sits in it, because your university's IT department may mention it and because job descriptions increasingly say "Fabric/Power BI" together.

The practical relationship is simple: Power BI remains the visualization and semantic-modeling layer; Fabric adds the heavy-duty data plumbing around it. A Fabric trial capacity (free for 60 days) gives you Premium-grade Power BI features — larger datasets, AI visuals, deployment pipelines, dataflows — without paying. If your thesis dataset grows past a few million rows, or you want to try the AI features in Chapter 12's "next steps," a Fabric trial is the legitimate free route. One caution: trial capacities expire, and anything depending on Premium features stops working when they do — so build your core thesis dashboard on standard features (everything in this book) and treat Fabric extras as experiments, not foundations. When in doubt, ask your supervisor or IT department whether the university holds a Fabric capacity you can use; many institutions now do through their Microsoft education agreements.

Common pitfalls

• Installing Desktop and expecting to share immediately. Desktop is the authoring tool; sharing happens through the Service, which needs an organizational email and (for sharing) a Pro license. Students often publish successfully and then cannot figure out why a colleague cannot see it — the answer is almost always licensing.
• Assuming the Service works with a personal Gmail. It does not. Microsoft blocks consumer email domains from Power BI Service sign-up.
• Thinking "BI = business only." The same tooling serves monitoring and evaluation dashboards for NGOs, public-health reporting, and university analytics. Your research project is a legitimate BI use case.
• Confusing Power BI with Excel. Power BI is not "Excel with better charts." It is a database engine plus a reporting layer. Trying to use it like a spreadsheet (cell-by-cell formulas, one-off manual fixes) leads to fragile reports that break on refresh.

For your research: Before you install anything, answer three questions in your research notebook: (1) Where does my data live today (Excel, CSV, Google Forms export, SQL database)? (2) Who needs to see the results — just my supervisor, or a committee, or the public? (3) Does my university email get me a Power BI Service account? The answers decide your sharing strategy for Chapter 10 and keep you from designing a report you cannot legally or practically distribute.

Key takeaways: - Power BI = Desktop (free authoring on Windows) + Service (cloud publishing and sharing) + Mobile (viewing). Desktop is where you build; the Service is where you share. - Desktop is free forever; the Service needs an organizational email, and sharing needs Pro — check your university's Microsoft 365 plan before spending money. - Power BI's value for researchers is the refreshable pipeline: define cleaning and calculations once, and your results stay current as data grows. - It complements rather than replaces Excel, Python, and SPSS — use each where it is strongest.

---

Chapter 2: Installing and Navigating Power BI Desktop

2.1 Installing Power BI Desktop

The installation is straightforward and takes about ten minutes on a typical laptop.

Step 1 — Get the installer. There are two official routes. The simplest is the Microsoft Store: open the Store app on Windows, search for "Power BI Desktop," and click Install. The Store version updates itself automatically, which is genuinely convenient. The alternative is the standalone installer from Microsoft's Power BI download page (search "Download Power BI Desktop" on the web). The standalone installer lets you choose when to update, which some labs prefer. Both are the same product; monthly updates mean the two rarely differ by more than a few weeks.

Step 2 — Run through setup. Accept the license, choose the install location (the default is fine), and wait. There is no configuration wizard and no database to install — Power BI Desktop is self-contained.

Step 3 — Sign in (recommended but optional). When you first open Desktop, it may invite you to sign in with your organizational account. You can skip this and use Desktop fully offline. Sign-in becomes necessary only when you publish to the Service (Chapter 10). If you have a university email, signing in now saves a step later.

System notes: Power BI Desktop requires Windows 10/11 (64-bit) and .NET components that the installer handles. For the datasets in this book, 8 GB of RAM is plenty. If you are on macOS, the practical options are a Windows VM (Parallels, VMware, or VirtualBox), a cloud Windows desktop, or your university's lab machines. Power BI does not run natively on Mac or Linux, and there is no announced plan for it to do so — this is the single biggest platform limitation, so settle your Windows access before Chapter 3.

2.2 The first-run experience

When you open Power BI Desktop, you see a welcome screen offering to get data, open recent files, and see what's new. Close it (or click "Get data" if you are eager — we will do that properly in Chapter 3). What remains is the main window, and it is worth learning its geography deliberately rather than clicking around at random.

2.3 The three views: Report, Data, Model

Down the left edge of the window are three icons. These are the three views, and each is a different room in the same house. You will switch between them constantly.

Report view (the top icon, looks like a bar chart) is where you design pages full of visuals. The large central area is the canvas — the page your readers will see. On the right side you have the Visualizations pane (choose chart types and drag fields into them) and the Fields pane (your tables and columns, listed like a folder tree). A Filters pane appears when you need it. Report view is your design studio; Chapters 6, 7, and 9 live here.

Data view (the middle icon, looks like a table) shows your actual data, table by table, exactly as it sits in the model after cleaning. You can sort, look at values, and — importantly — create new columns and tables here. But you cannot edit individual cells; the data here is the output of your Power Query steps, not a spreadsheet you type into. Use Data view as your inspection window: whenever numbers look wrong in a visual, come here and look at the raw rows.

Model view (the bottom icon, looks like a diagram) shows your tables as boxes connected by lines — the relationships. This is where you verify and fix how tables join together. A quick glance at Model view tells an experienced user whether a report's numbers can be trusted. Chapter 5 is entirely about getting this view right.

A useful habit: after every import and every transformation, spend thirty seconds in Data view and Model view checking that what you expected actually happened. Beginners who skip this build reports on broken data and discover it during their defense.

2.4 The ribbon and the essential panes

The top ribbon changes with the active view and the selected visual. In Report view the Home tab holds paste, get data, transform data, new visual, and publish. The Insert tab adds text boxes, buttons, shapes, and images. The Modeling tab is where you create measures, calculated columns, and calculated tables (Chapter 8). The View tab toggles panes and page settings like size and theme.

The right-hand Visualizations pane has three parts: the gallery of visual types (clustered bar, line, pie, card, and dozens more), the field wells (X-axis, Y-axis, Values, Legend, Tooltips — slots where you drop columns), and the format section (the paint-roller icon) where you control colors, labels, titles, and backgrounds. The Fields pane below or beside it lists every table; expand a table to see its columns, with different icons for text, numbers, dates, and measures (a small calculator icon).

Learn this keyboard habit early: Ctrl+Z undoes, and the Selection pane (View tab) lists every object on the page so you can select, hide, or rename overlapping visuals — indispensable once a page gets busy.

2.5 Saving and file types

Power BI Desktop saves as a .pbix file — a single file containing your data model, queries, measures, and report pages. Keep your .pbix files in a project folder with a clear name, e.g., thesis-dashboard-v03.pbix, and version them as you work (save copies before big changes). There is also a .pbit template format that saves everything except the data — useful for sharing a report structure with a colleague who will load their own dataset. Note: .pbix files store imported data, so they can grow large; a file with a few million rows can exceed a gigabyte. For this book's datasets, files will stay under a few megabytes.

2.6 Staying current: monthly updates

Microsoft updates Power BI Desktop every month with new features, new visuals, and interface tweaks. Screenshots in any book — including this one — will drift slightly out of date. The core concepts (queries, relationships, measures, visuals) are stable; only button placement and names occasionally move. If a click-path in this book does not match your screen exactly, search the ribbon for the same feature name — it is almost certainly still there under a nearby tab. The Microsoft Store version updating automatically is the easiest way to stay current.

Common pitfalls

• Losing track of which view you are in. New users try to drag fields onto visuals while in Data view, or try to edit data while in Report view. Check the left icon strip first.
• Editing data directly in Data view. You cannot. Data view is read-only for values; changes happen in Power Query (Chapter 4) or through new calculated columns (Chapter 8).
• Saving one .pbix and overwriting it repeatedly with no versions. Before any risky change — a big Power Query rewrite, a relationship overhaul — do File → Save As with a new version number.
• Ignoring the Model view. If your totals look wrong, the cause is in the model nine times out of ten. Glance at Model view before you start formatting.

2.7 Personalizing the workspace: options worth setting once

Power BI Desktop ships with sensible defaults, but five settings are worth changing on day one. Open File → Options and settings → Options:

1. Preview features — each month's experimental features live here. Leave them off while learning (they can change or vanish), but glance at the list every few months; today's preview is often next year's core feature.
2. Data Load → Auto date/time — Power BI automatically creates hidden date tables for every date column. This is convenient but clutters the model and confuses beginners (mysterious extra tables in the Fields pane). Many professionals turn it off and build an explicit calendar table instead (Chapter 5). For this book, turn it off now so your Fields pane stays clean.
3. Power Query Editor → Formula bar and column quality — make sure both are visible (Chapter 4 relies on them).
4. Regional settings — under Regional Settings, confirm the locale matches your data. Date formats like 01/02/2026 mean different things in US vs. UK/Pakistan locale; a mismatch here silently mis-parses dates at import. If your CSV uses day-month-year, set the locale accordingly before connecting.
5. Theme and canvas defaults — under Report settings, you can set a default theme and page size so every new report starts from your preferred look instead of the factory default.

These five minutes of configuration prevent an entire category of "why does my date look wrong" and "where did this table come from" questions later.

2.8 Your first fifteen minutes: a guided tour exercise

Before Chapter 3's real data arrives, spend fifteen minutes on this orientation drill with the free Power BI sample reports (File → Open → browse to the built-in samples, or download "Power BI Desktop samples" from Microsoft Learn). Open one and do the following, in order: (1) In Report view, click five different visuals and watch the others react — say out loud what filtered what. (2) Open the Filters pane and read every active filter on the current page. (3) Switch to Data view, pick the largest table, and sort two columns — notice you cannot edit cells. (4) Switch to Model view and trace one relationship line with your finger: which table is the "one" side? (5) Select a visual, open Edit interactions, and set one visual to None — observe the change, then undo. This drill builds the spatial memory — where things live — that makes every later chapter faster. Students who skip orientation spend the whole book hunting for panes; fifteen minutes here pays for itself by Chapter 4.

For your research: Set up a project folder now, e.g., Documents/thesis-dashboard/, with subfolders data/ (raw CSVs/Excel files — never edit these originals), pbix/ (versioned .pbix files), and notes/ (a text file where you log each transformation decision and why). When your methodology chapter asks "how was the data processed?", this folder is your answer. Reviewers and examiners love an auditable trail, and this habit gives you one for free.

Key takeaways: - Install from the Microsoft Store for automatic updates; Desktop is free and works offline. - Master the three views: Report (design), Data (inspect), Model (relationships). Check Data and Model views after every data change. - Learn the Visualizations pane (chart types + field wells + formatting), the Fields pane (your columns), and the Selection pane (managing objects on a page). - Version your .pbix files and keep raw data files untouched in a separate folder.

---

Chapter 3: Connecting to Data Sources — Excel, CSV, SQL, and the Web

3.1 What "connecting" really means

In Power BI, connecting to data is not the same as opening a file. When you connect, Power BI records a query: a saved recipe that says "go to this file, read these rows, apply these cleaning steps." The data is then loaded into Power BI's in-memory engine. This matters because the connection is live in a specific sense: if the source file changes and you press Refresh, Power BI re-runs the recipe and pulls the new data through all your cleaning steps automatically. That is the refreshable pipeline from Chapter 1, and it begins the moment you connect properly.

3.2 Your running example: the Clean Cooking Survey

For the rest of this book we will use one consistent dataset — a realistic MS-thesis-style survey. Imagine a research team studying household cooking fuel in a rural district. They surveyed 300 households and recorded: a household ID, the village name, the primary cooking fuel (firewood, charcoal, LPG, kerosene, biogas), monthly fuel spending, household size, whether anyone reported respiratory symptoms in the past year, and the survey date. This is exactly the kind of wide, messy, real-world table a student collects — with typos in village names, inconsistent capitalization of fuel types, a few blank spending values, and dates in mixed formats. We will import it now, clean it in Chapter 4, model it in Chapter 5, and build it into a dashboard by Chapter 12.

A small sample of what the raw CSV looks like:

HouseholdID	Village	Fuel	MonthlySpend	HHSize	RespSymptoms	SurveyDate
H001	Kotli	firewood	2500	6	Yes	2026-01-12
H002	kotli	Firewood	2800	5	no	12/01/2026
H003	Mirpur	LPG		4	No	2026-01-13
H004	Kotli	Charcoal	3100	7	YES	2026-13-01

Notice the problems already: "Kotli" vs "kotli", "firewood" vs "Firewood", a blank spend, an impossible date (month 13). Real data always looks like this. Power BI's job is to make it analyzable without you hand-editing 300 rows.

3.3 Connecting to Excel and CSV: the click path

This is the path you will use most as a student, so learn it precisely.

Excel: 1. In Power BI Desktop, go to the Home tab → click Get data → choose Excel Workbook → click Connect. 2. Browse to your .xlsx file and open it. A Navigator window appears listing every sheet and named table/range in the workbook. 3. Select the sheet or table you want. A preview appears on the right — check that the column headers look right and the first rows are real data, not title rows. 4. Click Load to import directly, or Transform Data to open Power Query first (recommended whenever the sheet is messy — which is usually). For our survey file, choose Transform Data.

CSV / text files: 1. Home → Get data → Text/CSV → Connect → browse to your .csv. 2. The preview dialog shows the delimiter Power BI detected (comma, semicolon, tab), the encoding, and the first 200 rows. This dialog is your first quality check: if columns look merged into one, the delimiter is wrong — change it in the dropdown before loading. 3. Again, prefer Transform Data over Load for real-world files.

Practical tips for file sources: Keep the source file in a stable location — if you move or rename it, the query breaks and you will need to fix the source path (in Power Query: right-click the first step, "Source," and point it at the new location). Use a relative-friendly folder structure (project folder with a data subfolder) so the path stays valid when you move the project. And never edit the raw file to fix data problems — fix them in Power Query instead, so the fix is documented and repeatable.

3.4 Connecting to a web page or web API

Power BI can pull tables directly from web pages — useful for public datasets, published statistics, and reference tables.

1. Home → Get data → Web → paste the URL → OK.
2. If the page contains HTML tables, the Navigator lists them; select and preview as with Excel.
3. Many government statistics portals and open-data APIs return JSON; Power BI parses JSON into tables you can expand in Power Query.

Caution for research: web sources change without notice. A table that exists today may be redesigned tomorrow, breaking your query. For a thesis, download a snapshot of web data into a CSV, cite the access date, and connect to the file. Use live web connections for monitoring dashboards, not for evidence you must reproduce.

3.5 Connecting to databases (SQL Server and others)

If your data lives in a university database or a lab SQL Server, the path is Home → Get data → SQL Server (or Oracle, MySQL, PostgreSQL under the Database category). You enter the server name and database name, choose credentials, and then either pick tables in the Navigator or write a SQL query. Writing your own SQL in the connection dialog (the "Advanced options" SQL statement box) is often the cleanest approach: the database does the heavy filtering and joining, and Power BI receives a tidy result set. You do not need to master SQL for this book, but if your lab uses databases, learning SELECT ... FROM ... WHERE ... pays off quickly.

3.6 Storage modes: Import vs. DirectQuery

When you connect, Power BI asks (or defaults to) a storage mode. This is a consequential choice.

• Import mode (the default): Power BI copies the data into its in-memory engine inside the .pbix file. Queries are blazing fast, all features work (including the full Power Query transformation set and all DAX), and the report works offline. The cost: data is only as fresh as your last refresh, and very large datasets can make the .pbix huge. For student research datasets — thousands to low millions of rows — Import is almost always the right choice.
• DirectQuery: Power BI leaves the data in the source database and translates every click on a visual into a live SQL query. Data is always current, and there is no .pbix size problem. The costs: slower interaction, some DAX functions and transformations are restricted, and the report needs a live database connection. DirectQuery only works with database sources, not CSV/Excel files.

There is also a Composite/Dual mode mixing both, but you do not need it yet. Rule of thumb: use Import unless someone with authority over a large database tells you otherwise.

3.7 Combining multiple files

Research data often arrives in pieces: one CSV per survey round, one Excel sheet per village, monthly exports from a device. Power BI handles this elegantly with folder and append patterns. Connect via Get data → Folder, point at the folder containing all the CSVs, and Power BI combines files with the same structure into one table — new files dropped into the folder appear on the next refresh. For a longitudinal study collecting monthly survey batches, this single feature can save you hours of copy-paste every month. We will use a two-round version of this in the capstone.

Common pitfalls

• Clicking Load instead of Transform Data on a messy file. Load is fine for clean files; for real survey data, always go through Power Query first. You can reopen Power Query any time (Home → Transform data), so a wrong choice is recoverable — but starting in Power Query saves rework.
• Wrong delimiter or encoding on CSV import. If Urdu or other non-Latin characters appear as gibberish, the encoding is wrong — in the CSV preview dialog, try UTF-8 (or UTF-8 with BOM). If all data lands in one column, change the delimiter.
• Moving the source file and breaking the query. Power BI stores the full file path. Keep a stable project folder structure from day one.
• Using DirectQuery "to keep data fresh" on a small CSV project. DirectQuery does not apply to files, and Import + scheduled refresh (Chapter 10) is the correct freshness mechanism for file-based research data.
• Title rows and merged cells in Excel. Survey workbooks often have a title in row 1 and headers in row 3. In Power Query, use "Use First Row as Headers" after removing top rows — Chapter 4 shows exactly how.

For your research: Create your dataset inventory today: a one-page table listing every data file for your study — filename, source (survey round, device, download), date range, number of rows, and who collected it. Tape it (figuratively) to your project folder. When you connect each file in Power BI, you will know exactly what you are looking at, and your methodology section practically writes itself: "Data were collected in three rounds (Jan–Mar 2026), stored as CSV exports, and integrated in Power BI via folder-based append."

Key takeaways: - Connecting creates a saved, refreshable query — not a one-time import. Refresh re-runs the whole pipeline. - For messy real-world files, choose Transform Data (Power Query) over Load. - Check delimiters, encoding, and headers in the preview dialog before loading — two minutes here saves two hours later. - Use Import mode for research datasets; use the Folder connector to combine multi-round survey files automatically. - Never edit raw source files to fix data — fix in Power Query so every fix is documented and repeatable.

3.8 Credentials, privacy levels, and refresh planning

Two connection settings trip up beginners the moment they combine sources — which you will do in the capstone — so learn them now.

Data source credentials: databases and web APIs need credentials (username/password, API key, or organizational login). Power BI stores them per data source: File → Options and settings → Data source settings. If a query that worked yesterday suddenly asks for credentials, open Data source settings, select the source, and choose Edit Permissions to re-enter them. Credentials also matter at publish time: the Service needs its own copy of database credentials (dataset → Settings → Data source credentials), and expired credentials are the #1 cause of scheduled-refresh failures (Chapter 10).

Privacy levels: when a query combines two sources (e.g., your CSV merged with a web lookup table), Power BI asks for each source's privacy level: Public, Organizational, or Private. This controls whether data from one source may be sent to another during operations like merging — a genuine data-protection feature. The safe student setting: mark your own research files Private or Organizational, and public reference data Public. If you get the cryptic error "the query references other queries or steps, so it may not directly access a data source," mismatched privacy levels are the cause — align them in Data source settings and the error clears.

Refresh planning checklist (fill this in for your project before Chapter 10): for each source, note where it lives (laptop vs. OneDrive vs. database), how often it changes, whose credentials it needs, and whether the Service can reach it. This one-page table prevents nearly every publishing surprise.

3.9 Excel specifics: sheets, tables, and named ranges

Excel workbooks deserve special attention because researchers live in them — and because how the data is stored in the workbook changes the Power BI experience. In the Navigator you will see three kinds of objects:

• Sheets (e.g., "Sheet1"): Power BI reads the whole sheet grid. If the sheet has title rows, blank rows, or footnotes mixed with data, you inherit all of it and clean it in Power Query. Workable, but noisy.
• Excel Tables (created with Ctrl+T): the gold standard. A proper Excel Table has one header row, no blank rows inside, and a name like tbl_Responses. Power BI reads exactly the table's current region — add rows next month and refresh picks them up automatically. Convert every research data sheet to a Ctrl+T table before connecting. It takes ten seconds and eliminates the most common Excel-import headaches.
• Named ranges: Power BI can read these too, but they are brittle — insert a row outside the range and it silently drops data. Prefer tables.

Two more Excel habits: (1) keep one table per sheet, with nothing else on the sheet — no summary calculations beside the data, no second table below; (2) never merge cells in a data table — merged headers become null columns in Power BI. If you receive a workbook that violates these rules (a supervisor's "final_final_v3.xlsx" always does), do the cleanup in Power Query per Chapter 4 rather than asking for a re-export — but do tell the data provider about Ctrl+T for next time.

---

Chapter 4: Power Query — Cleaning and Transforming Data Step by Step

4.1 What Power Query is and why it changes everything

Power Query is the data-cleaning engine inside Power BI (also available in Excel). When you clicked "Transform Data" in Chapter 3, you opened the Power Query Editor. Its genius is that every action you take — removing a row, fixing a typo, changing a data type — is recorded as a numbered step in the Applied Steps list on the right side of the window. The steps run in order, top to bottom, every time the data refreshes. This means your cleaning is not a one-time chore; it is a documented, repeatable, auditable program.

Behind the steps is a real programming language called M (the Power Query formula language). You will see M code in the formula bar at the top of the editor. You do not need to write M from scratch for this book — the interface writes it for you — but being able to read it helps you diagnose problems, and we will touch a few lines of it by Chapter 12.

4.2 The Power Query Editor layout

When the editor opens, learn its five zones:

1. Queries pane (left): every table/query in your model, listed by name. Rename queries here to meaningful names (double-click the name): SurveyResponses is better than Sheet1.
2. Data preview (center): the first 1,000 rows of your data after the currently selected step is applied. Click any step in Applied Steps and the preview shows the data at that point in history — this is your time machine for debugging.
3. Formula bar (top): the M code of the selected step. Toggle it via View → Formula Bar if hidden.
4. Applied Steps (right): the chronological recipe. Right-click any step to rename it ("Renamed columns" is more useful than "Renamed Columns1"), delete it, or insert a step after it.
5. Ribbon (top): the Home and Transform tabs hold almost everything you need.

The golden rule: never delete a step casually. Later steps often depend on earlier ones; deleting step 2 of 10 can break steps 3–10. If a step was a mistake, it is safer to right-click it and delete it deliberately, then check the steps after it.

4.3 The standard cleaning sequence for survey data

Let us clean our Clean Cooking Survey file. Here is the click path for each operation, in the order a professional would do them.

Step 1 — Promote headers and remove junk rows. Survey exports often have title rows. If row 1 says "Household Cooking Survey 2026" and real headers are in row 2: select the query, Home → Remove Rows → Remove Top Rows → enter 1 → OK. Then Home → Use First Row as Headers. Check: column names should now be HouseholdID, Village, Fuel, MonthlySpend, HHSize, RespSymptoms, SurveyDate.

Step 2 — Set correct data types. This is the most important and most skipped step. Click the small icon at the left of each column header and set: HouseholdID → Text; Village → Text; Fuel → Text; MonthlySpend → Whole Number (or Decimal Number if values have decimals); HHSize → Whole Number; RespSymptoms → Text (we will standardize it next); SurveyDate → Date. Wrong types cause silent disasters: a number stored as text will not sum, and a date stored as text will not sort chronologically. If Power Query shows errors (red rows) after changing a type, the preview tells you exactly which values failed — e.g., "2026-13-01" fails as a Date, which is how we catch the impossible date from Chapter 3.

Step 3 — Standardize text values. Select the Fuel column → Transform tab → Format → Capitalize Each Word, then fix remaining inconsistencies: select the column → Transform → Replace Values → replace "Firewood" variants. Better approach for many variants: right-click the column → Replace Values handles one value at a time, so for "firewood/Firewood/FIREWOOD" you might first apply Transform → Format → lowercase, then Replace "firewood" → "Firewood" once. For Village, do the same ("kotli" → "Kotli"). For RespSymptoms, standardize to "Yes"/"No": lowercase the column, then replace "yes" → "Yes", "no" → "No".

Step 4 — Handle missing and erroneous values. Select MonthlySpend → the blank cell: decide your research rule before you click. Options: (a) leave nulls (Power BI ignores nulls in averages — usually the honest choice); (b) filter them out (Home → Remove Rows → Remove Blank Rows — but this deletes whole respondents, document it); (c) replace with a value such as the median (Transform → Replace Values → replace null — type the word null in "value to find"). There is no universally right answer; there is only the answer you can defend in your methodology section. For the impossible date "2026-13-01": right-click the error cell → you can Remove Errors (Home → Remove Rows → Remove Errors) or replace it with null. Removing one bad row out of 300 is defensible; silently inventing a date is not.

Step 5 — Remove duplicates. Select HouseholdID → Home → Remove Rows → Remove Duplicates. If any rows disappear, you had duplicate submissions (common with online forms). Investigate before deleting: are they true duplicates or two visits to the same household? For our survey, HouseholdID should be unique, so duplicates are data-entry errors.

Step 6 — Split and merge columns. Survey data often packs two facts into one column ("Kotli – H001") or splits one fact across two ("FirstName", "LastName"). Select a column → Transform → Split Column → By Delimiter → choose the delimiter (dash, space, comma). To combine: select two columns (Ctrl+click) → Transform → Merge Columns → choose a separator. For dates stored as text in mixed formats, select the column → Transform → Date → Parse, or use Split Column and rebuild — mixed formats like "12/01/2026" (is that Dec 1 or Jan 12?) are genuinely ambiguous; resolve with the survey team and document the interpretation.

4.4 Reshaping: unpivot — the most powerful trick for questionnaire data

Questionnaires often export in wide format: one column per question, e.g., Q1_FuelCost, Q2_FuelCost, ... or one column per month: Jan_Spend, Feb_Spend, Mar_Spend. Analysis needs long format: one row per household per month, with columns Month and Spend.

The click path: select the columns to unpivot (click the first, Shift+click the last) → Transform tab → Unpivot Columns. Power Query creates two new columns, Attribute (the old column names) and Value (the numbers). Rename them to Month and Spend. This single operation turns an unanalyzable wide table into a clean fact table. If you take one advanced Power Query skill from this book, make it unpivoting — questionnaire data without it is nearly useless in Power BI.

4.5 Appending and merging queries

Append stacks tables with the same columns on top of each other (survey round 1 + round 2 + round 3 → one table). Home → Append Queries → choose the queries. Column names must match; mismatches create half-empty columns, which the preview reveals immediately.

Merge joins tables side by side on a key, like a database join (household responses + a village lookup table with district names). Home → Merge Queries → select both queries → click the key column in each (Village) → choose the join kind (Left Outer keeps all rows from the first table — the safe default for research). After merging, click the expand icon on the new column and tick only the columns you need from the second table.

4.6 Finishing: load settings and documentation

When the preview looks right: Home → Close & Apply. Power Query runs all steps and loads the clean table into the model. Two finishing touches: (1) In Power Query, right-click the query → Properties and write a one-line description ("Cleaned household survey, 298 rows after dedup, see notes 2026-10-08"). (2) Disable loading for helper queries you do not need as tables (right-click → uncheck Enable load) — e.g., a lookup table you only merged from.

Because every step is recorded, your methodology section can now say precisely: "Raw responses (n=300) were processed in Power BI Power Query: top title row removed, headers promoted, data types assigned, text fields standardized to title case, one duplicate HouseholdID removed, one invalid date (2026-13-01) and two blank spending values set to null, and monthly spending columns unpivoted, yielding an analysis table of 298 households." That paragraph is worth its weight in gold at a defense.

Common pitfalls

• Changing a data type and ignoring the errors. Red error rows are Power Query telling you about bad data. Investigate every one; "Remove Errors" without looking is how real findings get deleted.
• Cleaning by hand in Excel instead of in Power Query. Every manual edit is invisible and unrepeatable. If your supervisor asks "how did you handle the missing values?", "I fixed them in Excel" is a weak answer; "step 7 of the query, documented" is a strong one.
• Applying steps in the wrong order. Filter rows before expensive operations; set data types before replacing values that depend on the type. If a later step breaks, click through Applied Steps one by one to find where the preview first looks wrong.
• Forgetting that the preview shows only 1,000 rows. A problem in row 50,000 will not appear in the preview. Use column quality indicators (View → Column quality) to see error and null percentages across the whole column — the single most underused diagnostic in Power Query.
• Leaving the query named "Sheet1" or "Query1." Six months later you will not remember what it was. Rename everything.

4.7 Reading M: the formula bar is not scary

You do not need to write M from scratch, but reading the three lines Power Query writes most often will save you when the interface cannot express what you need. Click any step and look at the formula bar:

• A rename step looks like = Table.RenameColumns(#"Promoted Headers", {{"Village ", "Village"}}) — it says which table, which old name, which new name. If a rename breaks after a source change, you can edit the text directly instead of redoing the step.
• A replace step looks like = Table.ReplaceValue(#"Previous Step", "kotli", "Kotli", Replacer.ReplaceText, {"Village"}) — find, replacement, and the column it applies to. To standardize ten spellings at once, you can duplicate this pattern in the Advanced Editor (Home → Advanced Editor) rather than clicking Replace Values ten times.
• A custom column looks like = Table.AddColumn(#"Previous Step", "FuelCategory", each if [Fuel] = "Firewood" then "Biomass" else "Clean") — the each keyword means "for each row," and [Fuel] refers to the current row's value. This each ... [Column] pattern is 90% of the M you will ever read.

The Advanced Editor shows the whole query as one M script — each Applied Step is one line. Before any risky manual edit, copy the entire script into your notes/ file. If your edit breaks the query, paste the backup back. That is version control for Power Query, and it takes ten seconds.

One genuinely useful hand-written M trick: parameterize the source path. In the Advanced Editor, replace the hardcoded file path with a parameter (Home → Manage Parameters → New Parameter, e.g., DataFolder), so moving the project folder means updating one parameter instead of editing every query. Your future self, reorganizing folders the night before submission, will be grateful.

For your research: After this chapter, your cleaning pipeline is also your data-processing audit trail. Export it: in Power Query, right-click each query and review the Applied Steps with your supervisor once. Ask them: "Is setting blanks to null acceptable, or should I exclude those respondents?" Getting this sign-off before analysis prevents the most painful kind of revision — redoing results because the cleaning assumptions were never agreed.

Key takeaways: - Power Query records every cleaning action as a replayable step — your cleaning is a documented program, not a chore. - The standard sequence: remove junk rows → promote headers → set data types → standardize text → handle nulls/errors → deduplicate → split/merge → reshape (unpivot). - Set data types deliberately and investigate every error row; column quality indicators reveal problems the 1,000-row preview hides. - Unpivot wide questionnaire tables into long format; append rounds together; merge lookup tables with a left-outer join. - Every cleaning decision is a methodology decision — document it and get supervisor sign-off.

---

Chapter 5: Data Modeling Basics — Tables, Relationships, and the Star Schema

5.1 Why modeling matters more than visuals

Here is a truth that separates beginners from competent users: ugly reports with a correct model beat beautiful reports with a broken model. A wrong relationship silently produces wrong totals — and unlike a wrong chart color, nobody can see the error by looking at the page. Every mysterious number in Power BI ("why does the total show 900 when the parts add to 300?") traces back to the model. This chapter gives you just enough modeling theory to build models you can trust, without turning you into a database administrator.

5.2 Tables have roles: facts and dimensions

In a well-built model, tables play two roles:

• Fact tables hold the measurements — the things you count, sum, and average. In our survey: one row per household response, with MonthlySpend, HHSize, RespSymptoms, SurveyDate. Facts are usually long (many rows) and narrow-ish, full of numbers and foreign keys.
• Dimension tables hold the descriptions — the categories you slice by. Village (with district, region), Fuel (with fuel category: biomass vs. clean), Date (year, month, quarter). Dimensions are usually short (few rows) and wide-ish, full of text.

Why separate them? Because it keeps every definition in one place. If "biomass fuels = firewood + charcoal + kerosene" is defined once in the Fuel dimension, every visual that uses it agrees. If instead you type that logic into five different visuals, they will eventually disagree — and your defense will be the moment someone notices.

5.3 Relationships: the lines between tables

In Model view, drag Village from the fact table to Village in the Village dimension. A line appears. That line is a relationship, and it has three properties you must understand:

1. Cardinality — how rows match. One-to-many (1:*) is the normal, healthy kind: one village in the dimension matches many survey rows in the fact. Many-to-many (:) is a warning sign — it usually means a dimension is not actually unique and totals may double-count. If Power BI creates a many-to-many relationship, stop and fix the dimension (deduplicate it) rather than accepting it.
2. Cross-filter direction — which way filters flow. Single direction (dimension → fact) is the default and almost always what you want: selecting "Kotli" in a slicer filters the survey rows. Both directions lets filters flow both ways, which sounds convenient but creates ambiguity — with both-direction filters on multiple tables, Power BI can follow circular paths and produce subtly wrong results. Beginners should use single direction everywhere until they have a specific, understood reason to change it.
3. Active vs. inactive — Power BI allows only one active relationship between two tables; extras become inactive (dashed line) and are ignored unless a DAX function explicitly invokes them. Inactive relationships are common with dates (e.g., OrderDate vs. ShipDate) and are handled in Chapter 8.

The click path to inspect: open Model view → click a relationship line → the Properties pane shows cardinality, direction, and active status. Double-click the line to edit. To create: drag a column from one table box to the matching column in another. To delete: click the line, press Delete (then verify totals still work).

5.4 The star schema: your target shape

Arrange your model so the fact table sits in the center with dimension tables around it like points of a star:

        Village
           |
Fuel — SurveyResponses — Date
           |
      (Household attrs)

Each dimension connects directly to the fact with a single-direction one-to-many relationship. Dimensions do not connect to each other. This shape — the star schema — is the single most important modeling pattern in BI, and it exists for good reasons: filter flow is unambiguous, DAX measures behave predictably, and performance is excellent.

The anti-pattern is the snowflake (dimensions chained through other dimensions: Fact → Village → District → Region) and the spaghetti (fact tables joined to each other, both-direction filters everywhere). Snowflakes sometimes happen naturally; Power BI handles small ones fine, but flattening lookups into fewer dimensions keeps life simple. Spaghetti — especially fact-to-fact relationships — is where double-counting lives. If your Model view looks like a spiderweb, rebuild it as a star before building a single visual.

5.5 Building our survey model, step by step

Let us build the star for the Clean Cooking Survey:

1. Fact table SurveyResponses (from Power Query, Chapter 4): HouseholdID, Village, Fuel, MonthlySpend, HHSize, RespSymptoms, SurveyDate, Month (we will add Month in a moment).
2. Dimension DimVillage: in Power Query, right-click the SurveyResponses query → Reference (this creates a new query that starts from the cleaned table — edits to the original flow through). Name it DimVillage. Select the Village column → right-click → Remove Other Columns → Home → Remove Duplicates. Optionally merge in a district lookup. Disable nothing — this loads as a small table. Back in Model view, create the relationship DimVillage[Village] (1) → SurveyResponses[Village] (*), single direction.
3. Dimension DimFuel: same reference-and-dedupe pattern on the Fuel column, plus a custom column for the fuel category: Add Column → Conditional Column → "If Fuel equals Firewood/Charcoal/Kerosene then 'Biomass' else 'Clean'". Name it FuelCategory. This is the kind of derived classification that belongs in the model, not in five separate visuals.
4. Dimension DimDate (calendar table): dates deserve their own dimension because months, quarters, and years are analysis axes. The professional way: Modeling tab → New table and write a small DAX expression generating a date range, or in Power Query create a list of dates and expand year/month/quarter columns. At minimum, ensure SurveyDate is a true Date type, then use Power BI's automatic date hierarchy (Year → Quarter → Month → Day) which appears when you drag a date field into a visual. For serious time analysis, build an explicit calendar table — Chapter 8's time-intelligence measures assume one.
5. Verify: Model view should show one central fact with three dimensions, all 1:* single-direction. Click each line and confirm.

5.6 The "why is my total wrong?" diagnostic checklist

When a visual's total looks wrong, work this list in order — it resolves the vast majority of cases:

1. Check the relationship cardinality. A many-to-many where you expected one-to-many is the #1 cause of inflated totals.
2. Check filter direction. A both-direction relationship can let a slicer on one dimension unexpectedly filter another.
3. Check for blank rows in the visual. A "(Blank)" category means fact rows whose key matches nothing in the dimension — a data-quality leak (e.g., a village spelled a third way that the dimension lacks).
4. Check the aggregation. Is the visual summing a column that should be averaged (like HHSize)? Totals of ratios and averages need DAX measures, not raw sums — Chapter 8.
5. Check Data view. Sort the fact table and eyeball the rows behind the visual. The truth is always in the rows.

Common pitfalls

• Accepting auto-created many-to-many relationships. Power BI guesses relationships on load; its guesses about messy keys are often wrong. Verify every auto-created line in Model view.
• Relating fact tables directly to each other. Two fact tables (e.g., survey responses + clinic records) should relate through shared dimensions (village, date), not to each other. Direct fact-to-fact joins are the classic double-counting trap.
• Putting descriptive text in the fact table and slicing by it. It works until the spelling varies; then "Kotli" and "kotli" become two villages in your chart. Dimensions centralize the clean list.
• Both-direction filters "to make the slicer work." If a slicer does not filter a visual, the relationship path is broken somewhere — fix the path, do not open both directions as a workaround.
• No calendar table, then wondering why "year over year" fails. Time intelligence needs a proper date dimension (Chapter 8).

5.7 Inactive relationships and role-playing dimensions

Two modeling situations confuse every intermediate user; meeting them here inoculates you.

Inactive relationships appear when two tables need two different join paths. Our survey has one date column, but imagine adding FollowUpDate (when a household was revisited). You cannot have two active relationships between SurveyResponses and DimDate — Power BI activates the first and marks the second inactive (dashed line). Inactive relationships are not broken; they are dormant until a DAX measure explicitly calls them with USERELATIONSHIP:

Follow-up Households =
CALCULATE (
    COUNTROWS ( SurveyResponses ),
    USERELATIONSHIP ( SurveyResponses[FollowUpDate], DimDate[Date] )
)

The pattern: model every date path as a relationship, keep one active, and invoke the others by name in measures. This is far cleaner than duplicating the date table.

Role-playing dimensions are the alternative: instead of one date table with inactive relationships, you create two copies of the dimension (e.g., Survey Date and Follow-up Date, each a reference of DimDate in Power Query) with one active relationship each. Simpler DAX, more tables. For a student model, role-playing dimensions are usually easier to reason about — choose them until your table count gets unwieldy, then graduate to USERELATIONSHIP.

A final modeling hygiene list: hide every key column used only for relationships (right-click → Hide in report view) so report authors use dimensions, not raw keys; set Sort by column for month names (sort MonthName by MonthNumber, otherwise April sorts before January alphabetically); and mark every table with a description. A model where the Fields pane shows only clean, sorted, business-named columns is a model other people can actually use — including your future self.

5.8 Star vs. snowflake: when to break the rule

Chapter 5 prescribed the star schema strictly. Here is the honest nuance: snowflakes (dimension → dimension chains, like Village → District → Region as separate tables) are not evil — they are sometimes the natural shape of reference data, and Power BI handles them correctly. The cost is cognitive: filter paths get longer, DAX gets slightly trickier, and the Model view gets busier. The practical rule: normalize when the lookup is shared or maintained separately (a district table your whole lab uses deserves to be its own table), and denormalize (flatten) when the attribute is used only for slicing one fact (fold District and Region columns into DimVillage — one fewer table, simpler everything). For this book's survey, flatten. If your thesis later involves a proper institutional data warehouse with conformed dimensions, you will meet snowflakes there — and you will understand exactly what they cost and buy. Beginners should also know the term "factless fact table" — a table recording only that an event occurred (household attended a training session: HouseholdID + Date, no measures). It sounds odd, but attendance/coverage analysis runs on factless facts; if your study tracks participation, you will build one.

For your research: Draw your star schema on paper before you build it — boxes for tables, lines for relationships, and one sentence per table saying what one row represents ("one row = one household's survey response"). Show it to your supervisor. This diagram belongs in your thesis methodology chapter, and examiners understand it instantly. A student who can explain their star schema can defend their numbers; a student who cannot is defending on luck.

Key takeaways: - Separate fact tables (measurements) from dimension tables (descriptions); join them in a star schema with the fact at the center. - Relationships must be one-to-many, single-direction, active. Many-to-many and both-direction filters are warning signs — fix the model, do not work around it. - Build dimensions with the reference-and-dedupe pattern in Power Query; add classifications (like fuel category) as dimension columns. - When totals look wrong, diagnose in order: cardinality → direction → blanks → aggregation → raw rows.

---

Chapter 6: Your First Visuals — Bars, Lines, Cards, Tables, and Matrices

6.1 Thinking in questions, not charts

Beginners pick a chart type and then look for data to fill it. Professionals do the reverse: they state the question, then choose the visual that answers it with the least effort from the reader. Keep this mapping taped to your monitor:

Question	Best visual
How do categories compare? (fuel types by households)	Clustered bar / column chart
How does something change over time? (spending by month)	Line chart
What is the single headline number? (avg. monthly spend)	Card
What are the exact values behind a summary?	Table
How do two dimensions cross? (fuel × village)	Matrix
What share does each part hold? (fuel mix)	Donut / pie (use sparingly)
Where does it happen? (villages on a map)	Map (filled or bubble)
Am I on target? (clean-fuel adoption vs. goal)	KPI visual or Gauge

A bar chart answers "which is biggest?" at a glance; a table answers "what exactly?" — they serve different readers. Your thesis needs both: the chart for the story, the table for the audit trail.

6.2 Building your first bar chart: the full click path

Let us answer: How many households use each fuel type?

1. In Report view, click anywhere on the blank canvas (so no visual is selected).
2. In the Visualizations pane, click the Clustered bar chart icon. An empty visual appears.
3. In the Fields pane, expand DimFuel and tick Fuel. Power BI drops it into the Y-axis well. Expand SurveyResponses and tick HouseholdID. Power BI puts it in the X-axis well as "Count of HouseholdID" — it auto-aggregates to a count because HouseholdID is text.
4. The chart now shows one bar per fuel type. Click the Format section (paint roller) → Y-axis → turn it On and check the category order; → Data labels → On, so values print on the bars.
5. Rename the visual's title: Format → Title → type "Households by Primary Cooking Fuel (n=298)".

That is the whole loop you will repeat hundreds of times: select visual type → drag fields into wells → format → title. Every visual in Power BI works this way; only the wells change names.

6.3 Lines for time, cards for headlines

Line chart — spending trend by month: select the Line chart icon → drag DimDate[Month] (or the SurveyDate hierarchy's Month level) into X-axis → drag SurveyResponses[MonthlySpend] into Y-axis → click the dropdown on the field in the well and change the aggregation from Sum to Average (right question: "average spend per household per month," not total). Format → Markers → On, so each month is a visible dot.

Card — the headline number: select the Card visual → drag MonthlySpend into Fields, set aggregation to Average → Format → Callout value → increase font size, set decimal places to 0. A card should answer one question in under two seconds: "Average monthly fuel spend: Rs 2,850." Add a second card: count of households with respiratory symptoms. These two cards will become the top row of your dashboard (Chapter 12).

6.4 Tables and matrices: precision instruments

Table: select the Table visual → drag in Village, Fuel, MonthlySpend (Average), HouseholdID (Count). Click any column header in the visual to sort. Tables are for exact values and for the "show me the numbers" reader. Keep them narrow — five columns maximum — or they become wallpaper.

Matrix: select the Matrix visual → Rows: DimVillage[Village] → Columns: DimFuel[FuelCategory] → Values: MonthlySpend (Average). You now have a cross-tab: average spend for biomass vs. clean fuels in each village — the classic thesis table, but interactive. Expand/collapse with the +/- icons; turn on Row subtotals and Column subtotals in Format for the margins.

6.5 Maps and the KPI visual

Map: select the Map (or Filled map) visual → drag Village into Location → drag HouseholdID (Count) into Bubble size. Power BI geocodes the village names. Warning: ambiguous place names geocode badly ("Kotli" exists in multiple districts) — verify every bubble sits where it should, and for precise work add latitude/longitude columns to your village dimension and use those instead of names.

KPI: select the KPI visual → Indicator: a measure like clean-fuel adoption rate (Chapter 8) → Trend axis: Month → Target: your goal (e.g., 0.5 for 50%). The KPI shows the value, the trend sparkline, and distance to target in one compact tile — perfect for "are we meeting the program goal?" reporting.

6.6 Visual interactions: what happens when you click

Click a bar in your fuel chart and watch: every other visual on the page filters to that fuel. This cross-filtering is Power BI's superpower — the report is not a set of pictures, it is a linked exploration tool. Click again to clear. As the author, you control this: select a visual → Format → Edit interactions → then click another visual and choose Filter, Highlight, or None. Use None deliberately when cross-filtering would confuse (e.g., clicking a village should not re-filter the village map into meaninglessness). Test every page by clicking each visual and watching what changes — this is part of your quality check in Chapter 9.

6.7 Choosing well: small rules with big payoffs

• Bars beat pies. Humans compare bar lengths accurately and pie angles poorly. Use a donut only for 2–4 shares of a whole, never for ten categories.
• Start bar axes at zero. A truncated axis exaggerates differences and will be challenged in review.
• One message per visual. If a chart needs a paragraph of explanation, it is two charts.
• Sort intentionally. Sort bars descending for rankings ("which fuel dominates?"), chronological for time, alphabetical only for lookup tables.
• Limit categories. More than 7–10 bars becomes noise — group the tail into "Other" in the dimension.

Common pitfalls

• Wrong aggregation in the well. Sum of HHSize is meaningless; you want Average. Always click the field's dropdown in the well and confirm Sum/Count/Average/Min/Max matches the question.
• Implicit measures vs. explicit measures. Dragging a column into a visual creates an implicit measure Power BI manages. It works, but you cannot reuse or document it. Chapter 8 moves you to explicit DAX measures — start that habit early for anything important.
• Using the date hierarchy wrong. Dragging SurveyDate gives Year → Quarter → Month → Day drill levels (great). But mixing a hierarchy axis with non-date categories breaks sorting — keep time axes purely temporal.
• Map bubbles in the wrong country. Always sanity-check geocoding against known locations.
• Too many visuals per page. Five to seven visuals per page is the comfort limit; beyond that, readers skim and miss everything.

6.8 Beyond the basics: five visuals worth knowing

Once the core visuals are comfortable, these five solve specific research-presentation problems:

1. 100% stacked bar chart — shows composition and comparison together: each village's bar is the same length, segmented by fuel share. Perfect for "the fuel mix differs by village" — the story absolute counts hide.
2. Scatter plot — one dot per household: X = household size, Y = monthly spend, with fuel category as the legend and bubble size as a third variable. Reveals correlations and outliers at a glance ("large biomass households cluster top-right"). Add a trend line in Analytics (the magnifier icon in the Visualizations pane) — but remember Chapter 11's warning: a trend line is not a significance test.
3. Decomposition tree — an AI visual that lets readers drill down a hierarchy interactively ("Total → FuelCategory → Village → …"), with the AI suggesting the most interesting splits. Excellent for exploratory defense Q&A: "show me where the symptom rate concentrates" becomes three clicks.
4. Key influencers — another AI visual: point it at RespSymptoms and it reports which factors most increase the likelihood (e.g., "households using charcoal are 2.3× more likely"). Treat its output as hypothesis generation, then verify with proper statistics — but as a discussion starter in a defense, it is unmatched.
5. Smart narrative — auto-written textual summaries of the page that update with slicers (also used in Chapter 11). Generate one, then edit ruthlessly: the AI drafts, you publish.

A word on custom visuals from AppSource (the "…" in the visual gallery): hundreds exist — Gantt charts, word clouds, network diagrams. They are tempting, but each is third-party code with its own quality and (for Service use) certification status. For thesis work, prefer built-in visuals; reach for AppSource only for a chart type Power BI genuinely lacks, and verify it is Microsoft-certified before publishing.

6.9 The Analytics pane: reference lines, trends, and forecasting

Select a bar or line visual and look at the Visualizations pane: beside the Format (paint roller) icon sits the Analytics icon (a magnifier with a chart). This pane adds analytical overlays without touching your data:

• Constant line / Min / Max / Average line: draw a horizontal line at a meaningful value — the program's target spend, the overall average. On the "average spend by village" bar chart, an average line instantly shows which villages sit above or below the mean. Set its color to contrast with the bars and label it ("Overall avg: Rs 2,850").
• Trend line: on scatter plots, adds a regression line with confidence shading. Useful for exploration; label it as exploratory per Chapter 11's honesty rules.
• Forecast: on line charts with a date axis, Power BI can extend the line with a forecast (choose seasons and confidence interval). For survey data with strong seasonality (fuel spending peaks in winter), a forecast illustrates the expected trajectory — but present it as a projection under current conditions, never as a finding. Examiners respect a researcher who labels uncertainty; they punish one who hides it.
• Anomaly detection: on line charts, finds unexpected spikes/dips and explains contributing factors. Run it on your monthly trend once — if it flags the month the LPG subsidy started, you have independent confirmation of the event's impact.

These overlays are presentation-layer only: they do not change measures or filters, so they are safe to experiment with freely.

For your research: For each visual you build, write its question in the title or subtitle: "Average monthly fuel spend by village" beats "Chart1" and forces you to check that the visual actually answers it. When you later write your results chapter, these question-titles become your paragraph topic sentences — the dashboard literally outlines your writing. And never present a visual you cannot explain the aggregation of: if an examiner asks "is this a sum or an average, and over what population?", the answer must be immediate.

Key takeaways: - Start from the question, then pick the visual: bars for comparison, lines for time, cards for headlines, tables for exact values, matrices for cross-tabs. - The build loop is always: choose visual → drag fields into wells → set the right aggregation → format → write a question-based title. - Verify every aggregation (Sum vs. Average vs. Count) — the well dropdown is where silent errors hide. - Cross-filtering makes the page explorable; control it with Edit interactions and test by clicking everything.

---

Chapter 7: Filters, Slicers, and Drillthrough

7.1 The three levels of filtering

Everything in Power BI that narrows the data is a filter, but filters live at three levels, and confusing them causes real mistakes:

1. Visual-level filters affect one visual only. Select a visual → open the Filters pane → expand "Filters on this visual" → drag in a field and set the condition. Use for: "this particular chart should show only 2026 data."
2. Page-level filters affect every visual on the page. In the Filters pane, "Filters on this page." Use for: "this whole page is about District Kotli."
3. Report-level filters affect every page. "Filters on all pages." Use sparingly — typically for things like excluding test records everywhere.

The mental model: report ⊃ page ⊃ visual. A visual shows data passing through all three sieves. When a number looks wrong, the Filters pane is the second place to look (after the model, Chapter 5) — a forgotten page-level filter is a classic cause of "where did half my data go?"

There is also a fourth, sneakier filter: the slicer's selection itself, and a fifth: cross-filtering from clicking visuals (Chapter 6). All of them combine. The Filters pane shows their joint effect per visual, which is why learning to read it is a core skill.

7.2 Slicers: filters your readers can touch

A slicer is a visual whose only job is filtering the page. Insert → Slicer (or pick the Slicer icon in the Visualizations pane) → drag DimVillage[Village] into its Field well. You get a list of villages with checkboxes (or a dropdown — change style in Format → Slicer settings → Style). Tick "Kotli" and every visual on the page filters to Kotli. This is how you hand control to your reader: instead of building twelve pages (one per village), you build one page with a village slicer.

Slicer best practices: - Put slicers in a consistent spot — a slim panel on the left edge or a strip across the top. Readers should never hunt for them. - Use dropdown style when a slicer has many values (20+ villages); use list or buttons for few values (fuel categories). - Turn on "Select all" option thoughtfully: for required-choice slicers (one village at a time), consider forcing single-select in Format → Selection → Single select. - Sync slicers across pages: select the slicer → View tab → Sync slicers pane → tick which pages it appears on. A village slicer synced across all pages makes the whole report feel like one coherent instrument. This single feature elevates a report from "a pile of pages" to "an application." - Date slicers: drag SurveyDate into a slicer and choose the "Between" style for a date-range slider — ideal for "show me responses from January to March."

7.3 The Filters pane in depth

Open the Filters pane (View → Filters). For each filter you can set conditions: is / is not, contains, greater than, date ranges, Top N, and for text fields a search box. Two features deserve special attention:

• Top N filters: "Show me the top 5 villages by household count." Drag Village into visual-level filters → Filter type: Top N → Top: 5 → By value: drag HouseholdID (Count). This is how you build "top 5 / bottom 5" ranking charts without touching the data.
• "Require single selection" and locked filters: in the Filters pane, you can lock a filter (so readers cannot change it) or hide it. Useful when a page is defined as "biomass users only" and you do not want readers accidentally unfiltering it.

Always label filtered pages honestly. If a page has a page-level filter "FuelCategory = Biomass," the title should say "…among biomass-fuel households." A reader who does not know about the filter will misread every number on the page.

7.4 Drillthrough: from summary to detail

Drillthrough lets a reader right-click a data point and jump to a dedicated detail page filtered to that point. Example: on your summary page, right-click the "Firewood" bar → "Drill through" → "Household Details" → land on a page showing only firewood households in a table.

Setup click path: 1. Create a new page; name it "Household Details." Build a table visual with household-level columns. 2. In the Filters pane, find "Drill through filters on this page" → drag DimFuel[Fuel] into it. 3. Go back to the summary page → right-click any fuel bar → the drillthrough option appears. Power BI passes the filter automatically. 4. Add a Back button on the detail page (Insert → Buttons → Back) so readers can return.

Drillthrough is how you serve two audiences at once: the executive summary reader who never drills, and the examiner who right-clicks the suspicious bar and checks the underlying rows. For a thesis defense, a drillthrough detail page behind every summary chart is quietly powerful — it says "I have nothing to hide; the rows are right here."

7.5 Cross-filtering control and the "filter audit"

We touched Edit interactions in Chapter 6; here is the full audit routine. Select each visual → Format → Edit interactions → click every other visual and set Filter (cross-filter), Highlight (dim the rest), or None. Then perform the filter audit: click through every slicer value and every bar, and watch each visual. Ask three questions: (1) Does every visual respond sensibly? (2) Do the totals still reconcile (sum of parts = whole)? (3) Is there any selection that produces a blank page with no explanation? A blank visual under some filter combination is not a bug in Power BI — it is usually correct (no data matches) — but an unexplained blank looks like an error to a reader. Add a text box note or design the slicer defaults so the landing state always shows data.

7.6 Bookmarks and the selection pane: guided stories

Bookmarks (View → Bookmarks pane) capture the state of a page — slicer selections, visual visibility, sort orders. Create bookmark 1: "All villages, bar chart visible." Bookmark 2: "Kotli only, map zoomed." Then add buttons (Insert → Buttons) that jump between bookmarks. This turns your report into a guided narrative: "Click through the story: 1) Overall fuel mix → 2) Kotli deep dive → 3) Spending comparison." For a defense presentation, bookmarks are your slide deck inside the dashboard — no alt-tabbing to PowerPoint, and every "slide" is live data.

Common pitfalls

• Hidden page-level filters. The #1 "my numbers changed and I don't know why." Before defending any number, open the Filters pane and read all three levels.
• Slicers that do not affect a visual. Check Edit interactions — someone (you, last Tuesday) set it to None.
• Drillthrough page appearing in normal page navigation. Right-click the detail page tab → Hide page — drillthrough pages should be reachable only via drillthrough, keeping navigation clean.
• "Select all" slicer states that exclude new values. If new villages arrive on refresh, a slicer with explicit selections will not include them; prefer "Select all" default states for growing dimensions.
• Bookmark capturing a filter you did not intend. Always clear selections before creating a "default view" bookmark, and test each bookmark after creating it.

7.7 Persistent filter states: URLs and default views

Two advanced filtering techniques complete your control over what readers see.

Filter URLs: the Service lets you encode filter selections directly in a report's URL (append ?filter=Table/Column eq 'Value'). This means you can send your supervisor a link that opens the report already filtered to Kotli — no instructions needed ("click the slicer, then…"). The syntax is documented on Microsoft Learn; build the URL by filtering the report manually first, then use the Service's Share → Copy link with the filter applied, which generates it for you. Use this for "here is exactly the view I describe on page 47 of the thesis" links in your appendix.

Default views and personal bookmarks: readers with access can save personal bookmarks (their own saved filter states) without affecting your report — encourage this instead of letting collaborators ask you for "a version filtered to X." And remember that your published bookmarks define the landing state every reader sees first: set the default bookmark to the most representative view (all villages, full date range), never to a narrow slice that could mislead a casual opener.

Reset discipline: after demonstrating filters in a defense or meeting, reset to the default view before moving on. A forgotten "Kotli only" slicer left active while you discuss national-level findings is exactly how miscommunication happens — make resetting a visible, narrated habit ("and now back to the full sample").

7.8 Relative date filtering for longitudinal studies

Survey data collected over months raises a recurring question: "show me the last 3 months." Hard-coding date ranges in filters means editing the report every month. Relative date filters solve this: in the Filters pane, set a date field's filter type to Relative date — "is in the last 3 calendar months." The window rolls forward automatically on every refresh. Combine with a relative-date slicer (slicer → Filter type → Relative) so readers can switch "last 3 / 6 / 12 months" themselves. Two cautions: (1) relative filters depend on today's date, so a thesis figure captured in October describes a different window than one captured in December — always pair relative filters with a visible "data as of" label (Chapter 9's freshness card); (2) for the frozen thesis appendix, convert relative filters to fixed date ranges so the figure is reproducible years later. Rolling windows for monitoring, fixed windows for publication — that is the discipline.

For your research: Design your report's filter contract and write it down: which slicers exist, which pages they sync to, what the default selection is, and what each drillthrough path shows. Put this contract in your methodology appendix. It does two jobs: it proves to examiners that you controlled the analysis frame (no cherry-picked subsamples hiding behind an undeclared filter), and it lets a future researcher reproduce exactly what you showed. Reproducibility is not just about code — it is about every filter state behind every figure.

Key takeaways: - Filters stack at report → page → visual levels; the Filters pane shows their combined effect — read it before trusting any number. - Slicers hand filtering to the reader; sync them across pages and place them consistently. - Drillthrough pages give summary readers speed and skeptical readers the underlying rows — build both. - Audit every interaction: click everything, reconcile totals, and use bookmarks to turn the dashboard into a guided, defensible narrative.

---

Chapter 8: DAX Measures — Your First Five Measures

8.1 What DAX is and why measures beat spreadsheet formulas

DAX (Data Analysis Expressions) is the formula language of Power BI's engine — the same language used in Power Pivot for Excel and SQL Server Analysis Services. If Power Query's M language prepares the data, DAX analyzes it. Every total, average, percentage, and year-over-year comparison in your report is a DAX calculation.

The single most important concept in this chapter: a measure is not a value — it is a recipe for a value that is evaluated in context. Write Total Spend = SUM(SurveyResponses[MonthlySpend]) once, and Power BI evaluates it separately for every bar, every slicer selection, every matrix cell. Put it in a bar chart by village and each bar computes its own village's sum; filter to Kotli and the card recomputes for Kotli. You never write the formula twelve times for twelve villages. This "write once, evaluate everywhere" property is what makes DAX powerful — and what confuses spreadsheet users, who expect a formula to live in one cell with one answer.

8.2 Measures vs. calculated columns vs. Quick measures

• Measure: computed at query time, in the filter context of the visual. Use for aggregations: sums, averages, counts, percentages. Measures do not appear in Data view as columns; they live in the Fields pane with a calculator icon. This is what you want 90% of the time.
• Calculated column: computed once per row when the data refreshes, stored in the table, visible in Data view. Use for row-level logic: SpendPerPerson = [MonthlySpend] / [HHSize], or a category label derived from a row's values. Calculated columns increase file size and do not respond to slicers — do not use them for anything that should change with filters.
• Quick measures: prebuilt DAX templates (Home/Modeling → Quick measure) for common patterns like year-to-date totals. Genuinely useful for learning — generate one, then read the DAX it wrote to learn the pattern.

Rule of thumb: if the result should change when the reader clicks a slicer → measure. If it is a fixed property of the row → calculated column.

8.3 Creating a measure: the click path

Right-click the SurveyResponses table in the Fields pane → New measure. The formula bar appears with a placeholder. Type your DAX and press Enter. Rename it immediately (it defaults to "Measure"). Best practice: create a dedicated measure table (Modeling → New table, name it _Measures, define it as = {0} then delete the column — or simply create measures inside it) so all measures live in one findable place instead of scattered across tables.

8.4 Your first five measures, explained line by line

Measure 1 — Total households:

Households Surveyed = COUNTROWS ( SurveyResponses )

COUNTROWS counts rows in the current filter context. In a card with no filters: 298. In a bar chart by village: each bar's village count. One formula, every context.

Measure 2 — Average monthly spend:

Avg Monthly Spend = AVERAGE ( SurveyResponses[MonthlySpend] )

AVERAGE ignores blank/null values — which is exactly why Chapter 4's decision to leave blanks as null (rather than zero) matters. Nulls excluded: honest average. Zeros included: artificially low average. Your cleaning decision flows directly into your DAX result; document the link.

Measure 3 — Clean-fuel adoption rate:

Clean Fuel % =
DIVIDE (
    CALCULATE (
        COUNTROWS ( SurveyResponses ),
        DimFuel[FuelCategory] = "Clean"
    ),
    COUNTROWS ( SurveyResponses )
)

Three new ideas here. CALCULATE modifies the filter context — here it says "count rows, but only where fuel category is Clean." DIVIDE is safe division: it returns blank instead of an error when the denominator is zero (use it instead of / in every measure). Format this measure as a percentage: select it → Measure tools → Format → Percentage.

Measure 4 — Households with symptoms:

Symptom Households =
CALCULATE (
    COUNTROWS ( SurveyResponses ),
    SurveyResponses[RespSymptoms] = "Yes"
)

Same CALCULATE pattern, filtering on a fact-table column directly. Compare with Measure 3, which filtered via the dimension — both work; dimension-based filters are cleaner when the category logic lives in the dimension (Chapter 5).

Measure 5 — Year-over-year change (needs a date dimension):

Spend YoY % =
DIVIDE (
    [Avg Monthly Spend]
        - CALCULATE ( [Avg Monthly Spend], SAMEPERIODLASTYEAR ( DimDate[Date] ) ),
    CALCULATE ( [Avg Monthly Spend], SAMEPERIODLASTYEAR ( DimDate[Date] ) )
)

SAMEPERIODLASTYEAR shifts the filter context back one year — but it requires a proper date table marked as a date table (right-click DimDate → Mark as date table → choose the date column). Notice the measure references another measure ([Avg Monthly Spend]) — measures compose, which keeps complex logic readable. With monthly survey data spanning a year, this shows whether spending is rising or falling versus the same month last year.

8.5 Filter context and row context: the 80/20 of DAX theory

You need exactly two mental models, and this section is the 80/20:

• Filter context = "which rows are in scope right now?" Set by slicers, visual axes, filters, and CALCULATE. Every measure is evaluated inside a filter context. When you click "Kotli" in a slicer, the filter context becomes "village = Kotli," and every measure on the page recomputes within it.
• Row context = "which single row am I looking at?" Exists in calculated columns and iterator functions like SUMX. In a calculated column SpendPerPerson = [MonthlySpend] / [HHSize], the row context is the current row.

The classic beginner error is using a row-context expression where a filter-context one is needed, or vice versa. When a measure returns the same value in every row of a table visual, you almost certainly have a filter-context problem — the measure is ignoring the visual's axis. CALCULATE is the tool that manipulates filter context; iterators (SUMX, AVERAGEX) create row context inside measures. You do not need to master this today — but when a measure misbehaves, ask: "Is my filter context what I think it is?" and the answer is usually no.

8.6 Debugging DAX: a practical routine

1. Test the measure in a table visual with the relevant dimensions on rows — you can see every context's result side by side.
2. Decompose: replace parts of a complex measure with simpler ones (does the numerator work alone? the denominator?).
3. Check the format: a measure returning 0.42 displayed as a number instead of 42% is a formatting issue, not a DAX issue.
4. Use DAX Studio (free external tool) for serious debugging — it shows the query Power BI generates and timing. Optional for this book, essential later.
5. Read the error messages literally. "A single value for column X cannot be determined" means you used a column where DAX expected one value — wrap it in an aggregator (MAX, SELECTEDVALUE, VALUES).

Common pitfalls

• Using / instead of DIVIDE. Division by zero in one slicer combination produces an error that can blank a whole visual. DIVIDE everywhere.
• Summing an average. SUM(SurveyResponses[AvgCol]) is meaningless. Averages of averages need AVERAGEX over the right grain — or better, recompute from the base: total spend ÷ households.
• Calculated columns for things that should be measures. A "Clean Fuel Flag" as a calculated column is fine (row property); "Clean Fuel %" as a calculated column is wrong (it will not respond to slicers).
• Forgetting Mark as date table. Time-intelligence functions silently return wrong results without it.
• Measure names with spaces but no brackets in references. Always reference as [Measure Name]; better yet, name measures without ambiguity from the start.

8.7 Five more DAX patterns researchers reach for constantly

Once the first five measures work, these five patterns cover most research-reporting needs:

1. Conditional counting with multiple criteria:

High Spend Biomass HHs =
CALCULATE (
    COUNTROWS ( SurveyResponses ),
    DimFuel[FuelCategory] = "Biomass",
    SurveyResponses[MonthlySpend] > 3000
)

CALCULATE accepts multiple filter arguments — they combine with AND logic. This is how you define study subgroups ("biomass users spending above Rs 3,000") as reusable measures instead of one-off filters.

2. Distinct counts (avoiding double-counting):

Villages Covered = DISTINCTCOUNT ( SurveyResponses[Village] )

Use DISTINCTCOUNT, not COUNTROWS, when the question is "how many different villages" — counting rows would count households. The row-vs-distinct distinction is a classic defense question; knowing which function answers which question is the whole battle.

3. Ratios that need the right grain:

Avg Spend Per Person =
DIVIDE (
    SUM ( SurveyResponses[MonthlySpend] ),
    SUM ( SurveyResponses[HHSize] )
)

Note this is not the average of the Spend Per Person calculated column — it is total spend divided by total people, which is the correct population-level figure. Averaging per-row ratios gives each household equal weight regardless of size; dividing totals weights by people. Know which one your research question asks for, and state it.

4. Cumulative totals over time:

Cumulative Households =
CALCULATE (
    [Households Surveyed],
    FILTER (
        ALL ( DimDate ),
        DimDate[Date] <= MAX ( DimDate[Date] )
    )
)

ALL removes the current date filter, FILTER rebuilds it as "everything up to the current date" — the standard running-total pattern. Plot it as a line to show survey progress over the fieldwork period ("we reached 200 households by June").

5. Previous-period comparison for text-safe labels:

Selected Village Label =
SELECTEDVALUE ( DimVillage[Village], "All villages" )

Drop this into a card or title to make the report self-describing: the title reads "Average spend — Kotli" or "Average spend — All villages" depending on the slicer. Small touch, large professionalism payoff, and it directly supports Chapter 11's "what population is this?" defense.

For your research: Every measure is a operational definition of a concept in your study. "Clean-fuel adoption rate" is not just a formula — it is how your thesis defines adoption. Write each measure's definition in plain language in your methodology chapter: "Adoption rate was defined as the proportion of surveyed households reporting LPG or biogas as primary fuel, computed over all valid responses (nulls excluded)." An examiner who can read your DAX and your definition side by side has no room to doubt what you measured. Keep a measure dictionary — a table of measure name, DAX, plain-language definition, and unit — as an appendix. It is one page that dramatically raises the credibility of your quantitative chapter.

Key takeaways: - A measure is a context-aware recipe: write once, and it evaluates correctly for every bar, slicer, and filter. - Use measures for aggregations (they respond to filters); calculated columns only for fixed row-level properties. - CALCULATE changes filter context; DIVIDE prevents divide-by-zero errors; SAMEPERIODLASTYEAR needs a marked date table. - Every measure operationalizes a research concept — document each as a definition in your methodology, and keep a measure dictionary appendix.

---

Chapter 9: Formatting and Design — Making Reports Look Professional

9.1 Why design is a research skill, not decoration

A thesis examiner, a journal reviewer, and a conference audience all do the same thing in the first ten seconds with a figure: decide whether to trust it. That judgment is heavily influenced by visual professionalism — consistent fonts, aligned elements, honest axes, readable colors. This is not vanity; it is communication ethics. A sloppy dashboard suggests sloppy analysis, and a misleading one (truncated axes, cherry-picked colors) is a credibility risk. The good news: professional Power BI design is mostly a short checklist, not artistic talent.

9.2 Layout: the Z-pattern and the dashboard grid

Readers scan a page in a Z-pattern: top-left → top-right → diagonal to bottom-left → bottom-right. Place elements accordingly:

1. Top-left: title and the single most important headline (the key finding card).
2. Top row: 2–4 KPI cards — the "answer in ten seconds" strip.
3. Middle: the main charts (bars, lines) that develop the story.
4. Bottom: supporting detail — tables, matrices, maps.
5. Left edge or top strip: slicers, consistently placed on every page.

Use alignment and spacing deliberately: select multiple visuals (Ctrl+click) → Format tab → Align (lefts, tops, distribute). Equal-sized cards in a neat row read as "designed"; ragged edges read as "accidental." Turn on gridlines and snap-to-grid (View tab) and leave them on. Leave white space — a page packed edge-to-edge with visuals is unreadable; if everything is emphasized, nothing is.

9.3 Color: restraint, meaning, and accessibility

• One palette, whole report. Pick 3–5 colors maximum. Power BI themes (View → Themes → Browse for themes, using a JSON theme file) enforce this — define your palette once and every visual inherits it. Your university's colors are a natural choice.
• Color must mean something. Use color to encode a category (fuel types) or a status (above/below target) — never decoration. The same category must be the same color on every page (set this in the theme or via conditional formatting rules, not by hand per visual).
• Accessibility: roughly 1 in 12 men has color-vision deficiency. Avoid red/green as the sole differentiator; add labels or shapes as redundant encoding. Power BI has built-in color-blind-safe theme options — use them. Check contrast: light gray text on white fails readability; aim for dark text on light backgrounds or the reverse in dark themes.
• Backgrounds: subtle shading (very light gray cards on white) adds structure; busy background images destroy readability. When in doubt, white background, thin borders, generous spacing.

9.4 Typography and titles

• Two fonts maximum: one for titles, one for body/labels. Segoe UI (Power BI's default) is fine; your university's brand font is better.
• Title hierarchy: page title (large, top-left) → visual titles (small, above each visual) → axis labels and data labels (smallest). Every visual gets a question-based title (Chapter 6) — "Average monthly spend by village," not "Chart1."
• Numbers formatting: set decimal places deliberately (currency: 0 decimals; percentages: 1 decimal), use thousands separators, and keep units in the title or axis ("Rs" / "%") so readers never guess.
• Text boxes for narrative: Insert → Text Box. A 2–3 sentence insight under a chart ("Biomass households spend 18% more on average than clean-fuel households, driven by charcoal prices in Kotli.") turns a picture into a finding. This is the bridge between your dashboard and your results chapter — write these insights as you build, and your writing is half done.

9.5 The quality checklist: run this before anyone sees the report

Work through this list for every page, in order:

1. Titles: every visual titled as a question or finding; page title present.
2. Aggregations: spot-check two numbers per page against Data view (Chapter 5's diagnostic list).
3. Interactions: click every slicer value and every bar (Chapter 7's audit).
4. Filters: Filters pane shows no forgotten page/report-level filters.
5. Spelling and labels: no "Column1," no "Count of HouseholdID" as a displayed label — rename series in the visual's field well (double-click the field name there).
6. Mobile layout: View → Mobile layout — arrange a phone-friendly version; supervisors do check reports on phones.
7. Performance: if a page takes more than a few seconds, reduce visuals or check the model (Performance Analyzer: View → Performance analyzer → Start recording → refresh visuals — it shows exactly which visual is slow).
8. Freshness label: add a card or text box showing "Data as of" (a measure like MAX(SurveyResponses[SurveyDate])) so readers know the data's vintage.

9.6 Exporting figures for your thesis

Your thesis needs static figures, and Power BI produces them well: select a visual → … (more options) → Export data gives you the underlying numbers for replotting; for the image itself, use the Service's Export → PowerPoint/PDF, or screenshot at high zoom for crispness. Better: in the Service, Export → PowerPoint embeds the live data behind the slide. Always export from the final filtered state you describe in the caption, and keep the caption's numbers consistent with the figure — a caption saying "n=300" under a figure showing 298 rows is the kind of inconsistency examiners circle in red.

Common pitfalls

• Rainbow charts. Ten categories in ten bright colors is unreadable. Fewer colors, more labels.
• 3D effects, shadows, and gradient backgrounds. They add nothing and reduce readability. Flat, clean, professional.
• Truncated axes (bar charts not starting at zero) — misleading; reviewers will call it out.
• Tiny fonts that are illegible when projected or printed. Test at 100% zoom and on a projector if you will defend with one.
• Inconsistent number formats across visuals (one card shows "2850," another "2.85K") — set formats at the measure/column level so every visual agrees.

9.7 Building a reusable theme (JSON, without fear)

A theme is a small JSON file defining your palette, fonts, and default visual styles — and creating one is easier than it sounds. In Desktop: View → Themes → Customize current theme → adjust colors, then Export to save the JSON. Share that file with your lab and every member's reports match automatically.

A minimal theme palette for research work might be: a dark slate for text/axes, one strong accent (your university color) for the primary series, a muted secondary, a warm highlight for "target/goal" markers, and light grays for backgrounds and gridlines. Define data colors in order — Power BI assigns them to categories in sequence, so put your most important category's color first.

Two theme-adjacent habits multiply the payoff: (1) save a .pbit template (File → Export → Power BI template) containing your theme, page sizes, and standard slicer layouts but no data — every new project starts on-brand in one click; (2) keep the theme JSON in your lab's shared folder with a version number, so a palette change propagates deliberately rather than drifting visual by visual. Consistency across a lab's outputs is not bureaucracy — it is how a research group's work becomes recognizable, and recognizability builds trust with stakeholders who see report after report.

9.8 Designing for three outputs: screen, projector, print

Your dashboard will be consumed in three media, and each punishes different design sins:

• Screen (laptop/browser): the primary medium. Interactivity works; small text is readable; colors render accurately. Design here first.
• Projector (defense): washes out subtle colors, shrinks effective resolution, and hides anything below ~14pt. Before defense day: boost font sizes one step, increase color contrast (darker bars, bolder lines), and test on an actual projector if possible. Remove any element that is only legible when leaned toward — the examiners sit meters away.
• Print/PDF (thesis appendix): kills interactivity entirely. Slicers become meaningless, tooltips vanish, drillthrough dies. For the appendix export, set every slicer to its most representative state, add text stating the filter state under each figure ("Filtered: all villages, Jan–Dec 2026"), and confirm the PDF at 100% zoom is fully legible in grayscale — some examiners print in black and white, and your red/green encoding must survive as distinguishable shades or, better, as labeled values.

Build once for screen, then do a projector pass and a print pass as separate checklist runs. The fifteen minutes each takes is the cheapest insurance against "I can't read this" — the most deflating sentence in any defense.

For your research: Treat each dashboard page as a thesis figure with an interactive appendix. The discipline is the same as academic figure preparation: honest scales, labeled axes, defined populations, and a caption (your text-box insight) stating the finding. When your external examiner asks "how did you produce Figure 4.3?", the answer — "it is a live Power BI visual over the cleaned survey table; here is the measure definition and the filter contract" — is stronger than any static chart's provenance. Design is not the opposite of rigor; in presentation, design is rigor made visible.

Key takeaways: - Layout follows the Z-pattern: title + key finding top-left, KPI strip, main charts, supporting detail; align everything and leave white space. - Restraint in color (3–5 colors, meaningful encoding, color-blind-safe), two fonts max, question-based titles, deliberate number formats. - Run the quality checklist on every page: titles, aggregations, interactions, filters, labels, mobile layout, performance, freshness. - Add text-box insights under charts — they become your results-chapter prose — and export thesis figures from the exact filtered state you describe.

---

Chapter 10: Publishing to Power BI Service and Sharing

10.1 From Desktop to the cloud: the publish click path

Everything so far lived on your laptop. Publishing moves it to the Power BI Service where others can see it.

Prerequisites: you need (1) a Power BI Service account — sign up at app.powerbi.com with your organizational or university email (personal Gmail is rejected), and (2) to be signed in to Desktop with the same account (top-right corner → Sign in).

The click path: Home tab → Publish → choose a destination workspace (start with My Workspace) → OK. Desktop uploads the .pbix — data model, queries, measures, and report pages together. The upload takes seconds to minutes depending on data size. When it finishes, click the link in the success dialog to open the report in the Service.

What actually got created: in the Service workspace you now have two linked items — a dataset (the model: tables, relationships, measures) and a report (the pages of visuals). Understanding this split matters: you can build multiple reports on one dataset, refresh the dataset on a schedule independently of the report, and control access to each.

10.2 Workspaces: organizing shared work

My Workspace is personal — only you see it (with a free license, you cannot share from it at all). For collaboration, create a proper workspace: Workspaces → Create workspace → name it (e.g., "Thesis — Clean Cooking Study") → set it up. Workspaces hold datasets, reports, and dashboards together, and access is managed per workspace: Admin, Member, Contributor, Viewer roles. Give your supervisor Viewer (can view and interact, cannot edit), a co-author Contributor (can edit reports), and keep Admin for yourself. This role model is how you share without losing control — nobody can accidentally rewrite your model if they are only a Viewer.

10.3 Refresh: keeping the published data current

Your published dataset is a snapshot — it does not update itself until you configure refresh. Two mechanisms:

• Manual refresh: open the dataset in the Service → Refresh now. Fine for occasional updates.
• Scheduled refresh: dataset → Settings → Scheduled refresh → configure up to 8 daily refreshes (Pro). For file-based sources (our CSV/Excel), there is a catch: the Service runs in Microsoft's cloud and cannot see files on your laptop. You need an on-premises data gateway — a small app installed on a machine that stays on — to bridge your files to the cloud. For a student project, the practical pattern is simpler: keep the source files in OneDrive or SharePoint, connect Power BI to the cloud file path, and scheduled refresh works with no gateway. If your survey data lives in a shared OneDrive folder that your field team updates, your dashboard can refresh itself every morning — set this up once and your results stay current through the whole write-up period.

Refresh failures are the most common Service headache. When a refresh fails, the Service emails you with an error. The usual causes: the source file was moved/renamed, credentials expired (for databases), or a Power Query step broke on new data (e.g., a new fuel spelling). Fix it in Desktop, republish, and re-run refresh. Always check the refresh history (dataset → Refresh history) after changing anything upstream.

10.4 Sharing, done right

With Pro (or PPU), sharing options in a workspace:

1. Share the report directly: open the report → Share → enter email addresses → choose whether recipients can reshare or build on the dataset. The recipient gets a link; they need a Pro license too (in most configurations) to open it.
2. Workspace access: add people to the workspace with the Viewer role — they see everything in it. Best for a stable team (you + supervisor + co-authors).
3. Publish to web: File → Embed report → Publish to web (public) generates a public link anyone can open with no login. Never use this for real research data — it is truly public and indexable. It exists for public-facing demo dashboards and teaching examples only.
4. Embed in a thesis-adjacent site: a private embed in SharePoint or Teams keeps the dashboard inside your institution's walls.

Sharing etiquette for research: share the report, not the raw dataset, when the underlying rows are sensitive (household-level survey data often is). Use row-level security (RLS) if different people should see different slices — e.g., field coordinators see only their own district. RLS is defined in Desktop (Modeling → Manage roles) and enforced in the Service. Even a simple "supervisor sees all, district coordinators see their district" setup demonstrates serious data governance to an ethics committee.

10.5 Dashboards vs. reports in the Service (yes, the names collide)

In Power BI terminology, a report is multi-page and built in Desktop; a dashboard is a single-page collage built in the Service by pinning visuals from reports (hover a visual → pin icon → pin to dashboard). Dashboards can mix visuals from multiple reports and datasets, show live tiles, and are the classic "executive overview" surface. For your study: keep the detailed multi-page analysis as a report, and pin the 6–8 headline tiles (the KPI cards, the fuel-mix bar, the trend line) into a one-page dashboard for quick briefings. Chapter 11 designs this dashboard deliberately.

Common pitfalls

• Publishing from the wrong signed-in account. Desktop signed in with a personal Microsoft account cannot publish to your university tenant. Check the account in the top-right corner before publishing.
• "My supervisor can't open the link." Almost always licensing: sharing requires Pro on both sides (with free licenses, only Publish-to-web works — which you must not use for real data). Confirm license status with your IT department before promising access.
• Broken refresh because the laptop was closed. Scheduled refresh of local files needs the gateway machine online. OneDrive/SharePoint-hosted sources avoid this entirely.
• Sharing the dataset when you meant to share the report. Dataset sharing lets others build their own reports on your model — powerful, but give it deliberately, not accidentally.
• Forgetting that Publish overwrites. Republishing a .pbix replaces the Service dataset and report. If a colleague built something on your dataset in the Service, coordinate before republishing.

10.6 Subscriptions, alerts, and comments: the collaboration loop

Publishing is not the end of collaboration — the Service keeps working for you after the report is live.

Subscriptions: any viewer can subscribe to a report or dashboard (Subscribe → set schedule) and receive scheduled email snapshots — a PDF or PNG of the page in their inbox daily or weekly. For a supervisor who will not log in to check, a Monday-morning dashboard email keeps them effortlessly current during fieldwork. As the author, you can subscribe them (with Pro) so they do not have to set it up.

Data alerts: on dashboard tiles (cards, KPIs, gauges), viewers can set alerts — "email me if Symptom Rate exceeds 40%." For a longitudinal study, an alert on a key indicator turns the dashboard into a monitoring instrument: you hear about the problem when the data crosses the line, not at the next meeting. Set alerts on your own KPIs first to learn the mechanics, then offer them to stakeholders.

Comments: reports support threaded comments pinned to specific visuals — your supervisor can ask "why does Kotli spike here?" directly on the chart, and you reply in context. This beats a separate email thread where "the third chart on page 2" is ambiguous. Resolve comment threads as you address them; an inbox-zero comments panel is part of shipping.

Together these three features close the loop: publish → stakeholders get scheduled snapshots → alerts flag changes → comments capture questions → you iterate in Desktop and republish. That loop, running quietly in the background of your write-up months, is the real return on the hours you invested in Chapters 2–9.

10.7 Deployment pipelines: dev, test, and production for research teams

When two or more people build on the same dashboard — you plus a co-author, or a lab with several projects — editing the live report directly becomes risky. Power BI's deployment pipelines (Premium/PPU feature; check whether your university's capacity includes it) formalize a three-stage flow: Development (where you experiment), Test (where your supervisor reviews), and Production (the stable version stakeholders see). You deploy forward stage by stage, and each stage keeps its own data-source connections — so Test can point at a sample dataset while Production refreshes from the real one.

Most student projects do not need the full machinery, but adopt its discipline with a poor-man's version: keep three workspaces (Thesis-Dev, Thesis-Review, Thesis-Final), develop in Dev, copy the .pbix to Review for supervisor feedback, and publish to Final only when a milestone is signed off. Never edit Final directly — every change flows Dev → Review → Final. This single habit prevents the classic disaster of "I was fixing a typo and broke the defense dashboard," and it gives you a clean answer to "which version did the committee see?" — the one in Final, archived as PDF, on the date in question.

For your research: Decide your dissemination plan before you publish: Who gets access (supervisor? committee? public after defense)? At what granularity (full report? one-page dashboard? static PDF export)? On what timeline (live during write-up? frozen snapshot archived with the thesis)? A frozen, exported PDF of the final dashboard belongs in your thesis appendix as the citable record; the live dashboard serves the defense and ongoing collaboration. Write this plan down — ethics committees and supervisors both ask "who can see the data?", and this is your answer.

Key takeaways: - Publish from Desktop (Home → Publish) to a workspace; the Service splits your upload into a refreshable dataset and a viewable report. - Use real workspaces with roles (Viewer for supervisors); My Workspace is personal and unshareable on free licenses. - Keep source files in OneDrive/SharePoint for gateway-free scheduled refresh; monitor refresh history. - Share deliberately: reports for readers, workspace roles for teams, row-level security for sensitive slices — and never Publish-to-web with real research data.

---

Chapter 11: Dashboards for Research Reporting — Presenting Study Results

11.1 What a research dashboard is for

A business dashboard answers "how is the company doing?" A research dashboard answers "what did the study find, and can I trust it?" The audience is different — supervisors, examiners, reviewers, stakeholders — and so is the contract. Every number must be traceable to a defined population, every filter state must be declared, and the narrative must survive hostile questions. This chapter turns the report-building skills of Chapters 6–9 into a dashboard that can stand up in a defense.

11.2 Selecting findings: the one-page rule

A research dashboard is one page (or one page plus drillthrough detail). That constraint forces the hardest and most valuable decision: what are your 5–7 headline findings? For the Clean Cooking Survey, ours might be:

1. 298 households surveyed across 12 villages (sample frame).
2. 61% rely on biomass fuels (the problem statement, quantified).
3. Average monthly spend Rs 2,850 — biomass users spend 18% more (the economic finding).
4. 34% of households report respiratory symptoms; symptom rate is 2.1× higher among biomass users (the health finding).
5. Clean-fuel adoption rose from 28% to 39% over the survey year (the trend).
6. Kotli and two neighboring villages drive 70% of charcoal use (the geographic targeting finding).

Each finding becomes one tile: a card, a bar, a line, a map. If a finding does not fit in one tile, it is two findings. Write your findings as sentences first, then build tiles — never the reverse.

11.3 Structure: the research dashboard layout

Follow this proven layout top to bottom:

• Header band: study title, sample size and date range ("n=298 households, 12 villages, Jan–Dec 2026"), data-as-of date, and your name/institution. This band establishes provenance instantly.
• KPI strip (3–4 cards): the headline numbers a reader absorbs in ten seconds — households surveyed, biomass share, average spend, symptom rate.
• Evidence row (2–3 charts): the fuel-mix bar chart, the spend-by-village comparison, the adoption trend line. Each with a question-title and a one-sentence insight text box.
• Depth row: the matrix (fuel × village), the map, and the drillthrough-enabled summary bar. This row says "the detail is here if you want it."
• Footer band: methodology note ("Figures computed over valid responses; nulls excluded; see measure dictionary"), filter contract summary, and contact.

Pin these tiles from your report into a Service dashboard (Chapter 10.5) for the briefing version, and keep the full multi-page report behind it for the deep dive.

11.4 Narrative text and annotations

A dashboard without words is a Rorschach test — every reader sees a different story. Add text boxes with: (1) the finding in one sentence, (2) the population ("among biomass-fuel households"), and (3) the comparison ("vs. 28% in January"). Use Power BI's Smart narrative visual (Insert → Smart narrative) to auto-generate a textual summary that updates with filters — it writes sentences like "Average spend was highest in Kotli at Rs 3,400" and recomputes when slicers change. Review and edit its output; auto-text is a draft, not a publication. For key charts, add annotations: a line marking a policy date ("LPG subsidy introduced — March 2026") on the trend chart turns a wiggle into an explanation.

11.5 Making it defensible: the examiner's checklist

Before your defense, attack your own dashboard the way an examiner would:

1. "What population is this?" Every tile must imply or state its denominator. Add "(n=…)" to titles or the footer.
2. "Show me the rows." Every summary tile should have a drillthrough path to household-level detail (Chapter 7).
3. "What happens if I filter differently?" Demo the slicers live; show that the story holds across villages, not just in aggregate.
4. "How was this computed?" Have the measure dictionary (Chapter 8) open in a second tab.
5. "Is this statistically meaningful?" Be honest about what Power BI does not do: it shows descriptive patterns, not significance tests. If the difference matters, run the test in SPSS/R/Python and cite the p-value in the insight text — the dashboard presents, the statistics software confirms. Never claim significance from a bar chart alone.

11.7 Ethics, privacy, and governance for research dashboards

A dashboard that displays human-subjects data carries obligations a business dashboard does not. Work through these before sharing anything beyond your supervisor:

1. De-identification. Household-level drillthrough tables can indirectly identify families in small villages ("the only 9-person household in Kotli using biogas"). Remove or coarsen direct identifiers before publishing: drop HouseholdID from shared views, aggregate small villages, and consider whether even the combination of slicer selections could isolate an individual. When in doubt, share the aggregated report and keep the row-level detail in a private workspace.
2. Consent alignment. Your ethics approval covered specific uses of the data. A public-facing dashboard, or sharing with a new stakeholder group, may exceed the original consent — check the approval letter's wording before widening access. "Participants consented to academic publication" does not automatically cover a live dashboard on the open web.
3. Least privilege. Give each person the narrowest access that serves their role (Chapter 10's roles, plus row-level security for district coordinators). Review the access list quarterly; stale access outlives its purpose.
4. Data retention. University policies typically require retaining research data for years and eventually destroying identifiers. Your refresh pipeline complicates this: a live dashboard fed by OneDrive keeps old data visible indefinitely. Plan the endgame — at project close, freeze a final PDF, archive the .pbix and raw files per policy, and revoke sharing links.
5. Screenshot discipline. Warn every viewer that dashboard screenshots shared on social media or in presentations can leak filter states and underlying data. The footer note "do not redistribute screenshots of row-level views" costs one line and prevents real incidents.

None of this is bureaucracy for its own sake — it is the same research-ethics reasoning your proposal already passed, extended to the new medium. An examiner who sees a governance note in your methodology footer does not think "overkill"; they think "this researcher is careful."

11.8 From dashboard to paper: writing up the results

The dashboard is built; now it must become prose. Work tile by tile through your one-page dashboard, and for each tile write one paragraph following this template:

1. What the tile shows (one sentence, with the population): "Among the 298 surveyed households, 61% reported biomass as their primary cooking fuel (Figure 4.1)."
2. The pattern (one sentence, with the number): "Average monthly fuel spending was Rs 3,120 for biomass users versus Rs 2,640 for clean-fuel users, a difference of 18%."
3. The interpretation (one careful sentence): "This gap is consistent with rising charcoal prices reported in Kotli during the survey period, though the cross-sectional design cannot establish causality."

Note what the template enforces: every claim carries its denominator, every number matches the tile exactly, and interpretation stays inside what descriptives can support. Copy each tile's insight text box (Chapter 9) as the paragraph's starting draft — you wrote those sentences when the finding was fresh, which is when they were most accurate. Number the exported figures to match the thesis (Figure 4.1, 4.2…), store the exports in a figures/ folder with the filter state recorded in each filename (fig4-1_fuel-mix_all-villages_2026.png), and cross-check every in-text number against the figure one final time before submission. The dashboard-to-paper pipeline, run this way, makes numerical inconsistency — the most common and most embarrassing thesis error — nearly impossible. 6. "Can I reproduce this?" The .pbix, the raw CSVs, the query steps, and the filter contract — the full audit trail from Chapters 3–7.

11.6 Presenting: dashboard as defense aid

Project the dashboard, not slides, for your results section. Use bookmarks (Chapter 7) as your talking points: click "Finding 1," tell the story, click "Finding 2." When an examiner interrupts with "what about village X?", you do not fumble for a backup slide — you click the slicer and answer from live data. That moment, handled calmly, is worth more than ten polished slides. Rehearse the three most likely hostile questions with the dashboard open, and know exactly which slicer or drillthrough answers each.

Common pitfalls

• Dashboard as data dump. Twenty tiles is not thorough; it is unreadable. Five to seven findings, ruthlessly chosen.
• Findings without denominators. "34% report symptoms" means nothing without "of 298 surveyed households." Every percentage needs its population stated or one click away.
• Hiding uncertainty. If the sample is small in one village, say so in the insight text. Examiners trust researchers who state limitations more than those who bury them.
• Live-demo fragility. For the actual defense, have a PDF export of the dashboard as backup (Service → Export → PDF). Live demos fail at the worst moments; the PDF is your parachute.
• Overclaiming from descriptives. The dashboard shows what the data looks like; causal and significance claims need proper statistical testing reported alongside.

For your research: Your dashboard is the visual abstract of your thesis. Write the one-page dashboard before you write the results chapter: the discipline of choosing 5–7 findings and stating each in one sentence gives you the chapter's skeleton. Many students discover, while building the dashboard, that two of their "findings" are the same finding, or that a claimed pattern vanishes under a different filter — that discovery during building is infinitely cheaper than discovering it during the defense Q&A.

Key takeaways: - A research dashboard is one page, 5–7 findings, each stated as a sentence before it becomes a tile. - Structure: provenance header → KPI strip → evidence charts with insight text → depth row (matrix, map, drillthrough) → methodology footer. - Annotate with narrative: insight sentences, Smart narrative drafts you review, event markers on trends. - Make it defensible: denominators everywhere, drillthrough to rows, live slicer demos, the measure dictionary at hand, honest limits of descriptive analysis — and a PDF backup for demo day.

---

Chapter 12: Capstone — Build a Complete Dashboard from Raw CSV to Published Report

12.1 The mission

You now have every skill. This chapter strings them together into one continuous build, start to finish, with nothing skipped. Set aside two to three uninterrupted hours. By the end you will hold a published dashboard and a complete audit trail — the full pipeline from raw CSV to shareable evidence.

Starting materials: two CSV files in your project data/ folder — survey_round1.csv (Jan–Jun 2026, ~150 rows) and survey_round2.csv (Jul–Dec 2026, ~150 rows), in the messy format of Chapter 3 (inconsistent capitalization, a few blanks, one bad date, some duplicate IDs across rounds). If you have been following along, these are your files; if not, create them now from the Chapter 3 sample pattern.

12.2 Phase 1 — Connect and combine (20 minutes)

1. Home → Get data → Folder → point at data/ → OK. The preview lists both CSVs.
2. Click Transform Data (not Load). In Power Query, click Combine Files (the "Combine" button in the Content column header). Power Query samples the first file, builds the transformation, and applies it to both.
3. In the sample-file query it creates, do the Chapter 4 cleaning once — it flows to both files: remove top title row, promote headers, set data types, lowercase-then-standardize Fuel and Village, handle the blank spends (nulls) and the bad date (null), remove duplicate HouseholdIDs.
4. The combine step appends both rounds automatically. Verify row count ≈ 298 in the status bar.
5. Rename the query SurveyResponses, add a query description, Close & Apply.

Checkpoint: Data view shows one clean table, ~298 rows, correct types. If the count is off, click through Applied Steps to find where rows were lost.

12.3 Phase 2 — Model (25 minutes)

1. Model view: confirm the single table loaded.
2. Create DimVillage: right-click SurveyResponses → Reference → keep only Village → Remove Duplicates → rename query. Add a District column via Merge with a small village_lookup.csv (left outer join), or via a conditional column if you know the mapping.
3. Create DimFuel: reference → keep Fuel → dedupe → Add Column → Conditional Column → FuelCategory ("Biomass" for Firewood/Charcoal/Kerosene, else "Clean").
4. Create DimDate: Modeling → New table → build a calendar covering 2026-01-01 to 2026-12-31 with Year, Quarter, MonthNumber, MonthName columns → right-click → Mark as date table.
5. Drag relationships: DimVillage[Village] 1 → * SurveyResponses[Village]; DimFuel[Fuel] 1 → * SurveyResponses[Fuel]; DimDate[Date] 1 → * SurveyResponses[SurveyDate]. All single-direction. Confirm no many-to-many, no dashed inactive lines.
6. Hide key columns you never want in visuals (right-click → Hide in report view) to keep the Fields pane clean — hide the raw Village/Fuel columns in the fact table so authors always use the dimensions.

Checkpoint: Model view shows a clean three-point star. Write the one-sentence-per-table definitions in your notes.

12.4 Phase 3 — Measures (20 minutes)

Create a _Measures table and add the five measures from Chapter 8: Households Surveyed, Avg Monthly Spend, Clean Fuel %, Symptom Households, plus a symptom-rate measure:

Symptom Rate = DIVIDE ( [Symptom Households], [Households Surveyed] )

and a spend-per-person calculated column in the fact table:

Spend Per Person = DIVIDE ( SurveyResponses[MonthlySpend], SurveyResponses[HHSize] )

Format: percentages as %, currency as whole numbers. Build a table visual with Village on rows and all measures as values — verify every number by spot-checking two villages against filtered Data view rows.

Checkpoint: the table's grand total row reconciles; percentages are between 0 and 100%; no divide-by-zero errors under any slicer combination you can think of.

12.5 Phase 4 — Report pages (45 minutes)

Build three pages:

Page 1 — "Overview" (the dashboard page): title band with study name, n, and date range. KPI strip: Households Surveyed, Clean Fuel %, Avg Monthly Spend, Symptom Rate cards. Slicers: Village (synced to all pages), FuelCategory, date-range slider. Evidence: bar chart "Households by fuel type," line chart "Clean-fuel adoption % by month," map of villages sized by households. One insight text box per chart.

Page 2 — "Spending analysis": matrix of Village × FuelCategory with average spend; bar chart "Average spend by village" sorted descending; card showing max-village vs min-village gap. Page-level filter: none — let slicers do the work.

Page 3 — "Household details" (drillthrough target): table with HouseholdID, Village, Fuel, MonthlySpend, HHSize, RespSymptoms. Add drillthrough filters for Fuel and Village. Hide this page from navigation. Add Back buttons.

Run the full Chapter 9 quality checklist and the Chapter 7 filter audit on every page.

12.6 Phase 5 — Publish and share (20 minutes)

1. Save versioned .pbix: pbix/clean-cooking-dashboard-v1.pbix.
2. Move the two CSVs to your OneDrive project folder; in Power Query, update the Folder source path to the OneDrive location (so scheduled refresh works without a gateway).
3. Home → Publish → workspace "Thesis — Clean Cooking Study" (create it first in the Service).
4. In the Service: dataset → Settings → Scheduled refresh → daily 6 AM. Check Refresh history after the first run.
5. Pin the Page-1 headline tiles into a one-page dashboard ("Clean Cooking — Headlines").
6. Share the report with your supervisor as Viewer; export a PDF to notes/ as the frozen citable record.
7. Write the methodology paragraph (Chapter 4's template), the filter contract (Chapter 7), and the measure dictionary (Chapter 8) — three short documents that make the dashboard defensible.

12.7 What you have built, and what comes next

You now own a complete, refreshable, shareable research-data pipeline: raw CSVs → documented Power Query cleaning → star-schema model → DAX measures → interactive report → published dashboard with scheduled refresh. New survey responses dropped into the OneDrive folder flow through the entire pipeline untouched by hand. That is not a student exercise — it is professional-grade BI, and it is exactly the artifact that separates a thesis with "tables pasted from Excel" from one with a living evidence base.

Next steps beyond this book: learn DAX iterators (SUMX, AVERAGEX) for weighted calculations; explore calculation groups for consistent time intelligence; try Power BI's AI visuals (Key influencers, Decomposition tree) for exploratory analysis of why patterns occur; connect Python/R visuals for custom statistics inside the dashboard; and study deployment pipelines if your lab runs dev/test/prod workspaces. The Definitive Guide to DAX (see References) is your next book.

Common pitfalls (capstone-specific)

• Skipping checkpoints. Each phase's checkpoint exists because errors compound — a modeling error found in Phase 5 costs ten times what it costs in Phase 2.
• Cleaning in the combined query instead of the sample query. In a Folder-combine, transformations belong in the sample-file query so they apply to every file.
• Forgetting to re-point the source to OneDrive before publishing. Local paths break Service refresh; do the re-pointing before you publish, then refresh once to confirm.
• Publishing v1 and stopping. The first publish is a draft. Share with your supervisor, collect feedback, iterate — the pipeline makes iteration cheap.

12.8 Capstone troubleshooting: when the build fights back

Even a careful build hits snags. Here are the five you are most likely to meet in this capstone, with the fix for each:

1. "Column 'X' not found" after combining files. The two rounds have slightly different headers ("Village" vs "Village "). The combine used the first file's headers; the second file's variant became an error. Fix: in the sample query, add a rename step mapping every variant to the canonical name before the combine step runs — or better, standardize headers in the raw files' first rows and re-run.
2. Row count drops unexpectedly after merge. You merged the village lookup with an inner join instead of left-outer, silently dropping villages missing from the lookup. Fix: change the join kind to Left Outer, then filter the resulting null district values to find which villages lack lookup entries — that gap is data to fix, not rows to lose.
3. The date slicer shows months but the line chart is flat. SurveyDate in the fact is text, or the relationship to DimDate is inactive. Fix: verify the column type is Date in Power Query, verify the relationship line is solid (active) in Model view, and confirm DimDate is marked as the date table.
4. Refresh fails in the Service with "credentials" or "privacy" errors. You published before re-pointing the Folder source to OneDrive, or the privacy levels of the folder source and the combined queries mismatch. Fix: Data source settings in Desktop — set the folder path to the OneDrive location, align privacy levels, republish, re-enter credentials in the Service dataset settings.
5. A measure returns blank for one village. Almost always a data gap (no rows for that village in the filtered context) rather than a DAX bug — verify with a table visual showing the raw rows. If the blank is legitimate, decide how to display it: leave blank (honest), or wrap with COALESCE([Measure], 0) only when zero is truly the right interpretation. Document the choice.

Work each problem with the diagnostic habits from Chapters 5 and 8: check the model first, then the filters, then the rows. The capstone is not just a build exercise — it is where those habits become reflexes.

12.9 Presenting the capstone: a ten-minute demo script

Finish by demonstrating your dashboard to someone — a peer, a supervisor, a study group. Use this script:

• Minutes 0–2 (provenance): "This dashboard covers 298 households across 12 villages, surveyed January to December 2026. Data refreshes automatically when new responses arrive in our shared folder." Show the header band and the data-as-of card.
• Minutes 2–5 (findings): Walk the KPI strip top to bottom, one sentence per tile: "Sixty-one percent of households rely on biomass fuels. They spend 18% more per month than clean-fuel users. Thirty-four percent report respiratory symptoms — twice the rate among clean-fuel households."
• Minutes 5–7 (exploration): Click the Kotli bar. "Watch every visual refilter — this is live, not screenshots." Change the date slicer to the last quarter. Reset to the full sample, narrating the reset.
• Minutes 7–9 (depth): Right-click the charcoal bar → drill through to household details. "Every summary number opens to its underlying rows — nothing is hidden."
• Minutes 9–10 (method): Show the measure dictionary and the Applied Steps list. "Every cleaning decision and every calculation is documented and replayable."

Then invite the hardest question in the room and answer it with a slicer. That moment — a live answer from real data — is the entire point of this book.

For your research: The capstone is a rehearsal for your real thesis pipeline. Replace the survey CSVs with your actual data files and repeat these six phases — you already know every click. Time-box each phase as listed; if a phase overruns, it is telling you where your real data is messiest, which is itself a methodology insight worth writing down. When your supervisor asks "how long until the dashboard is ready?", your answer is now an evidence-based estimate, not a guess.

Key takeaways: - The end-to-end pipeline: Folder-connect → clean once in the sample query → star-schema model → DAX measures → three report pages → publish → scheduled refresh → pinned dashboard → shared + PDF archived. - Checkpoints after every phase catch errors where they are cheap. - Source files belong in OneDrive/SharePoint before publishing so refresh works without a gateway. - Ship the audit trail with the dashboard: methodology paragraph, filter contract, measure dictionary. That trail is what makes the work defensible.

---

Learning Dashboard

Keep this section open in a second tab while you work through the book — it is the quick-reference companion to the twelve chapters. The visual chooser answers "which chart?", the Power Query table answers "how do I fix this?", the sharing comparison answers "how do I distribute this?", the DAX table answers "which function?", the error decoder answers "what went wrong?", and the workflow map answers "where am I in the project?" Print these pages and pin them above your desk; experienced Power BI users still consult cheat sheets daily, and there is no prize for memorizing what a reference table holds better.

Visual chooser matrix

If your question is…	Use this visual	Key well settings	Watch out for
Which category is biggest/smallest?	Clustered bar chart	Category on axis, measure on values, sort descending	Start axis at zero; ≤10 categories
How does it change over time?	Line chart	Date on X-axis, measure on Y, markers on	Keep axis purely temporal; don't mix hierarchies
What's the headline number?	Card	Single measure, large callout value	One number per card; format units
Am I on/off target?	KPI visual	Indicator measure, trend axis, target value	Needs a real target, not a guess
What are the exact values?	Table	≤5 columns, sortable headers	Wide tables become wallpaper
How do two dimensions cross?	Matrix	Rows + columns + values, subtotals on	Check the aggregation of the values
What share of the whole?	Donut chart	2–4 categories max	Never for many categories; bars compare better
Where does it happen?	Map (bubble/filled)	Location + size; lat/long preferred	Verify geocoding; ambiguous names misplace
Why is this happening?	Key influencers / Decomposition tree	Categorical explainers + numeric measure	Exploratory only; validate statistically
What does the text say?	Smart narrative	Auto-summary of the page's visuals	Edit the draft; don't publish raw output

Power Query transformation quick-reference

Messy-data symptom	Power Query fix	Where to click
Title rows above headers	Remove Top Rows → Use First Row as Headers	Home tab
Numbers stored as text / dates as text	Set data type via column header icon	Column header icon
"Kotli" vs "kotli" vs "KOTLI"	Format → lowercase, then Replace Values	Transform tab
Blank cells in numeric column	Decide: keep null / remove rows / replace value	Right-click column
Impossible values (month 13)	Inspect error rows → Remove Errors or set null	Home → Remove Rows
Duplicate submissions	Remove Duplicates on the ID column	Home → Remove Rows
Two facts in one column	Split Column → By Delimiter	Transform tab
Two columns that belong together	Merge Columns (Ctrl+click both)	Transform tab
One column per month/question (wide)	Unpivot Columns	Transform tab
Monthly files to combine	Folder connector → Combine Files	Get data → Folder
Add reference table (districts)	Merge Queries → Left Outer → Expand	Home tab
Only see 1,000 rows in preview	View → Column quality / Column profile	View tab

Sharing options comparison

Option	Audience	License needed	Data visibility	Best for
My Workspace (unshared)	Just you	Free	Private	Drafting and learning
Workspace + Viewer role	Supervisor, committee, team	Pro (both sides, usually)	Controlled, login required	Thesis collaboration
Direct report sharing	Named individuals	Pro	Controlled link	One-off reviews
Publish to web (public)	Anyone on the internet	Free	Fully public — no login	Demos and teaching only; never real data
Export to PDF / PowerPoint	Anyone you send the file	Free	Frozen snapshot in the file	Thesis appendix; defense backup
Embed in SharePoint/Teams	Organization members	Pro / Premium	Inside institutional walls	Departmental reporting

DAX function quick-reference (the ones this book used)

Function	What it does	Example pattern
SUM / AVERAGE / COUNTROWS	Basic aggregation in filter context	Avg Monthly Spend = AVERAGE(SurveyResponses[MonthlySpend])
CALCULATE	Evaluates an expression under modified filters	CALCULATE(COUNTROWS(SurveyResponses), DimFuel[FuelCategory]="Clean")
DIVIDE	Safe division (blank on divide-by-zero)	DIVIDE([A],[B]) — always prefer over /
SAMEPERIODLASTYEAR	Shifts dates back one year (needs date table)	CALCULATE([Measure], SAMEPERIODLASTYEAR(DimDate[Date]))
SUMX / AVERAGEX	Row-by-row iteration inside a measure	SUMX(SurveyResponses, [MonthlySpend]/[HHSize])
FILTER	Returns a filtered table for iterators/CALCULATE	CALCULATE([M], FILTER(DimVillage, [District]="Kotli"))
SELECTEDVALUE	The single selected value, or blank/alternate	SELECTEDVALUE(DimVillage[Village], "All villages")
MAX / MIN	Latest/earn date or extreme value	MAX(SurveyResponses[SurveyDate]) for "data as of"

DAX error-message decoder

Error message (paraphrased)	What it usually means	Fix
A single value for column X cannot be determined	You used a bare column where one value was expected	Wrap in an aggregator: MAX, MIN, or SELECTEDVALUE
Function DIVIDE expects… / division by zero	A / hit a zero denominator in some filter context	Replace / with DIVIDE
The expression refers to multiple columns…	A measure returned a table where a scalar was needed	Check for a missing aggregator or misplaced FILTER
Column 'X' cannot be found	Renamed/removed column, or wrong table prefix	Check the Fields pane name; fix the reference
SAMEPERIODLASTYEAR returned blank	No marked date table, or gaps in the date column	Mark as date table; ensure continuous dates
Circular dependency detected	Two calculated columns/measures reference each other	Break the cycle: compute one from base columns
The query exceeded available resources	Cartesian explosion — usually a bad many-to-many	Fix the relationship cardinality first

End-to-end workflow map: from raw data to defended thesis

Stage	Book chapter	Your deliverable	Done when…
Plan	1	Dataset inventory + sharing plan	Every file listed; license confirmed
Set up	2	Project folder, versioned .pbix, options set	Folder structure + notes file exist
Connect	3	Saved queries to all sources	Refresh re-runs cleanly
Clean	4	Applied Steps audit trail	Methodology paragraph drafted
Model	5	Star schema diagram	All 1:*, single-direction; totals reconcile
Visualize	6	Draft report pages	Every visual has a question-title
Interact	7	Slicers, drillthrough, bookmarks, filter contract	Filter audit passed
Measure	8	Measure dictionary	Every number has a defined DAX + definition
Design	9	Quality checklist passed; mobile layout	Projector + print passes done
Publish	10	Workspace, scheduled refresh, shared report	Supervisor can open; refresh history green
Report	11	One-page research dashboard + PDF	5–7 findings, denominators everywhere
Ship	12	Archived PDF + raw files + notes	Reproducible by a stranger

---

Glossary

• Aggregation — combining many values into one: sum, average, count, minimum, maximum. The operation behind nearly every visual.
• Analytics pane — the Visualizations-pane section adding reference lines, trend lines, forecasts, and anomaly detection to a visual.
• Applied Steps — the ordered, replayable list of transformations in Power Query; the audit trail of your data cleaning.
• Bookmark — a saved state of a report page (filters, selections, visibility) used for guided navigation and presentations.
• Calculated column — a DAX column computed once per row at refresh time; a fixed property of the row, unaffected by slicers.
• Canvas — the report-page design surface in Report view where you arrange visuals.
• Cardinality — how rows relate between tables: one-to-many (healthy), many-to-many (warning sign).
• Cross-filter direction — which way filters flow across a relationship; single direction (dimension → fact) is the safe default.
• Cross-filtering — clicking a data point in one visual filters the other visuals on the page.
• Dashboard (Service) — a single-page collage of pinned tiles from one or more reports; distinct from a Desktop report.
• Data label — the numeric value printed on or beside a chart element (e.g., on each bar).
• Dataflow — a cloud-based Power Query pipeline in the Service for shared, reusable data preparation (beyond this book's scope).
• Dataset — the published data model in the Service (tables, relationships, measures), refreshable on a schedule.
• DAX (Data Analysis Expressions) — the formula language for measures and calculated columns in Power BI.
• Dimension table — a table of descriptive categories (village, fuel, date) used for slicing; the points of the star schema.
• DirectQuery — a storage mode where Power BI queries the source database live instead of importing data.
• Drill down / drill up — moving to a finer (year → month) or coarser level of a hierarchy inside a visual.
• Drillthrough — right-clicking a data point to jump to a detail page pre-filtered to that point.
• Fact table — the central table of measurements (one row per observation) in a star schema.
• Field well — a slot in the Visualizations pane (X-axis, Values, Legend…) where you drop columns to build a visual.
• Fields pane — the right-hand list of tables, columns, and measures available for visuals.
• Filter context — the set of active filters (slicers, axes, CALCULATE) in which a DAX measure is evaluated.
• Filters pane — the panel showing visual-, page-, and report-level filters and drillthrough filters.
• Gateway (on-premises data gateway) — a bridge app letting the Service refresh datasets from local files or databases.
• Import mode — the default storage mode: data is copied into the .pbix and the in-memory engine; fast and fully featured.
• Legend — the visual key mapping colors/symbols to categories, driven by the field in the Legend well.
• M language — the formula language behind Power Query steps, visible in the formula bar.
• Measure — a DAX calculation evaluated in filter context; the correct tool for aggregations that must respond to slicers.
• Model view — the Desktop view showing tables as boxes and relationships as lines; where model correctness is verified.
• Navigator — the preview window shown when connecting to Excel, web, or database sources.
• Parameter — a named value in Power Query (e.g., a folder path) that queries reference, so changes propagate from one place.
• Power Query Editor — the data-cleaning interface ("Transform Data"); every action becomes a replayable step.
• Query folding — Power Query's ability to push transformation steps back to the source database as SQL; irrelevant for files, valuable for databases.
• Quick measure — a prebuilt DAX template for common calculations; useful for learning patterns.
• Report — a multi-page set of visuals built in Desktop and published to the Service.
• Row-level security (RLS) — roles restricting which rows each user can see; defined in Desktop, enforced in the Service.
• Selection pane — the View-tab panel listing every object on a page for selecting, hiding, renaming, and reordering.
• Slicer — a visual that filters the page (or synced pages) when the reader makes selections.
• Slicer sync — the View-tab setting controlling which pages a slicer appears on and whether its selections stay in sync.
• Star schema — a model with one central fact table joined to dimension tables by one-to-many, single-direction relationships.
• Theme — a JSON-defined palette and style set enforcing consistent colors and fonts across a report.
• Tooltip — the popup shown on hovering a data point; customizable with additional fields in the Tooltip well.
• Unpivot — reshaping wide data (one column per month/question) into long format (one row per value); essential for questionnaire data.
• Visualizations pane — the right-hand panel with the visual gallery, field wells, format, and analytics sections.
• Workspace — a cloud container in the Service holding datasets, reports, and dashboards, with role-based access.

---

Practice Exercises

1. Install and explore. Install Power BI Desktop, open it, and identify the Report, Data, and Model views, the Visualizations pane, and the Fields pane. Save an empty .pbix in a new project folder with data/, pbix/, and notes/ subfolders.
2. Connect. Create a small CSV (20 rows) mimicking the Chapter 3 survey sample — include two inconsistent spellings, one blank value, and one bad date. Connect via Get data → Text/CSV → Transform Data.
3. Clean. In Power Query, fix all the problems in your CSV: promote headers, set types, standardize text, handle the blank and the bad date, remove duplicates. Rename every Applied Step clearly and write a query description.
4. Unpivot. Build a wide-format CSV with columns Jan_Spend, Feb_Spend, Mar_Spend plus HouseholdID. Unpivot it into Month/Spend long format and verify the row count triples (minus headers).
5. Model. From your cleaned table, create DimVillage and DimFuel via reference-and-dedupe, add a FuelCategory conditional column, and wire a three-table star schema with single-direction one-to-many relationships. Screenshot Model view.
6. Visualize. Build one page with a bar chart (households by fuel), a line chart (average spend by month), two cards, and a table. Give every visual a question-based title and run the Chapter 9 quality checklist.
7. Interact. Add a village slicer synced across two pages, a drillthrough detail page for fuel types with a Back button, and two bookmarks forming a mini guided story. Perform the filter audit: click every slicer value and confirm totals reconcile.
8. Measure up. Write the five Chapter 8 measures from scratch without looking, then add a sixth of your own (e.g., maximum household size among symptomatic households). Test each in a table visual across villages.
9. Research dashboard. Take a real dataset from your own field — coursework survey results, lab experiment logs, or a public dataset in your discipline — and build the one-page research dashboard from Chapter 11: provenance header, KPI strip, evidence row with insight sentences, methodology footer. Write the accompanying measure dictionary and filter contract.
10. Capstone publish. Complete the Chapter 12 capstone end to end with your own data: folder-connect (or equivalent), star schema, measures, three pages, publish to the Service (or export PDF if you lack a Service account), scheduled refresh configured or documented, and a one-page methodology note describing every cleaning decision. Present it to a peer or supervisor and record their three hardest questions — then answer each with a slicer, a drillthrough, or a measure definition.

---

References

[1] M. Ferrari and A. Russo, The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd ed. Redmond, WA, USA: Microsoft Press, 2019.

[2] M. Ferrari and A. Russo, Analyzing Data with Power BI and Power Pivot for Excel. Redmond, WA, USA: Microsoft Press, 2017.

[3] A. Ferrari and M. Russo, Introducing Microsoft Power BI. Redmond, WA, USA: Microsoft Press, 2016.

[4] R. Collie and A. Singh, Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016. Uniontown, OH, USA: Holy Macro! Books, 2015.

[5] M. Webb, Power Query for Power BI and Excel. New York, NY, USA: Apress, 2014.

[6] B. Powell, Mastering Microsoft Power BI: Expert Techniques for Effective Data Analytics and Business Intelligence. Birmingham, UK: Packt Publishing, 2018.

[7] D. Knight, B. Knight, M. Davis, and P. Rockwell, Microsoft Power BI Complete Reference: Bring Your Data to Life with the Powerful Features of Microsoft Power BI. Birmingham, UK: Packt Publishing, 2018.

[8] S. Few, Information Dashboard Design: Displaying Data for At-a-Glance Monitoring, 2nd ed. Burlingame, CA, USA: Analytics Press, 2013.

[9] E. R. Tufte, The Visual Display of Quantitative Information, 2nd ed. Cheshire, CT, USA: Graphics Press, 2001.

[10] Microsoft Learn, "Power BI documentation," Microsoft, 2024. [Online]. Available: https://learn.microsoft.com/en-us/power-bi/

---

End of Book 23. Next: Book 24 — Data Visualization Principles for Research Communication.