Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧭 Module 0 · Data Analytics & Power BI Orientation Beginner LESSON 03 / 72

The Complete Analytics Workflow

24 min 5 quiz questions 1 practical challenge 3 syllabus topics

There is one pipeline behind every Power BI project ever built. Once you can see it, the course stops being a list of features and becomes a sequence you understand the purpose of. This lesson walks all fourteen stages, and — more usefully — tells you what goes wrong at each one, because that is what you will actually spend your time on.

Lesson 3 of 72 · Data Analytics & Power BI Orientation 4%
01

Learning Objective

Beginners learn Power BI as a pile of disconnected features. Professionals see one pipeline with known failure points. This lesson gives you the second view.

By the end of this lesson you will be able to
  • Name every stage of the Power BI pipeline in order and say what it produces
  • Explain why the stages must happen in that order
  • Identify the most common failure at each stage before it happens to you
  • Decide which stage a given problem belongs to — the skill that makes debugging fast
  • Map each stage to the module of this course that teaches it
02

Why This Matters

Consider a very common situation. Your revenue total on the dashboard is wrong. Where do you look?

Without the pipeline in your head, you poke at the visual, then the measure, then the filters, more or less at random. With it, you work backwards through the stages in order and find the fault in minutes:

  1. Is the visual filtered by something you forgot? Check the Filters pane.
  2. Is the measure wrong? Test it in a card with no filters at all.
  3. Is the relationship dropping rows? Check for blank keys on the fact side.
  4. Did Power Query filter rows out? Compare the loaded row count to the source.
  5. Is the source itself incomplete? Check the file.
The debugging habit this builds
Always work backwards from the symptom through the pipeline, and check the row count at each stage boundary. Nine times out of ten the fault is one stage earlier than where you noticed it. Analysts who do this look fast; they are just being systematic.

The order also matters because each stage constrains the next. You cannot write a working time intelligence measure without a date table in the model. You cannot build a date table relationship without a clean date column from Power Query. You cannot get a clean date column if the source stores dates as inconsistent text. Skipping backwards is not possible; it just produces something that appears to work.

03

Concept

The pipeline

Fourteen stages, in order. Select any of them below to see what it produces and how it typically fails.

Three phases worth naming

The fourteen stages group into three phases with genuinely different characters, and recognising which phase you are in tells you how to behave.

The three phases
PhaseStagesCharacterHow long it takes
PrepareSources → Get Data → Power Query → CleanDetective work. Slow, unglamorous, full of judgement calls. Nobody sees this work but everything depends on it.Typically the largest single block of project time
ModelSemantic model → Relationships → DAX → MeasuresArchitecture. Decisions here are expensive to reverse later, so think before you build.Substantial, and worth not rushing
DeliverVisuals → Report → Interaction → Optimisation → Publish → DecisionCommunication and craft. Fast to iterate, and where stakeholder feedback lands.Feels like most of the work; usually is not
The trap beginners fall into
Prepare feels like a chore, so people rush it to get to the visuals. Every hour saved there costs several later, because a modelling problem caused by dirty data surfaces as a wrong number in a visual — the furthest possible point from its cause. Slow down at the start.

What each stage actually produces

A stage is complete when it has produced its output and you have verified it. Verifying as you go is the difference between a two-hour project and a two-day one.

Stage outputs and how to verify them
StageProducesHow you verify it
Get DataA connection and a previewRow and column counts match the source
Power QueryClean, typed, correctly-shaped tablesColumn quality shows no unexpected errors or nulls; row count is explained
Semantic modelFact and dimension tablesEvery table has a clear role; no orphan tables
RelationshipsA working filter pathSlicing a fact measure by a dimension column returns sensible, varying numbers
DAX / MeasuresBusiness definitionsEach base measure matches a manual aggregation of the source
VisualsEncoded numbersThe chart type matches the question; totals match the measure
ReportA readable pageSomeone unfamiliar can state the headline in 30 seconds
InteractionFollow-up capabilityEvery slicer and drill actually does something useful
OptimisationAcceptable speedPerformance Analyzer shows no visual dominating the load
PublishShared, refreshing contentSomeone else can open it and the refresh succeeds
DecisionA changed actionSomeone tells you what they did differently

Where the stages map onto this course

Pipeline stage to module
StagesModule
Data sources, Get DataModule 2 — Connecting to Data
Power Query, Clean & ShapeModule 3 — Power Query
Semantic model, RelationshipsModule 4 — Data Modeling
DAXModule 5 — DAX Fundamentals
MeasuresModule 6 — DAX for Real Analysis
VisualizationsModule 7 — Visualization
Report designModule 8 — Dashboard Design
InteractionModule 9 — Interactive Reports
OptimisationModule 11 — Advanced Power BI
Publish & shareModule 12 — Power BI Service
DecisionModules 10 and 14 — Projects, Portfolio & Career
04

Visual Explanation

Watch the same data move through the pipeline. Below is a single row's journey from a raw CSV cell to a number an executive reads.

One value, six stages
StageWhat the value looks likeWhat happened to it
Source CSV"1,249.50"Text, with a thousands separator
Get Data"1,249.50"Loaded as text — Power BI does not guess
Power Query1249.5Type changed to Decimal Number with correct locale
Model1249.5 in Sales[NetSales]Stored in a compressed column
DAXSUM(Sales[NetSales]) = 6,311,491.41Aggregated across 15,067 rows
Visual$6.31MFormatted, and placed in context by a trend line
Where this journey usually breaks
At the Power Query step. If the file was produced in a locale where the comma is a decimal separator, a naive type change turns 1,249.50 into an error or into 124950. The value then flows silently through every later stage and the executive reads a number that is a hundred times too large. Module 3 Lesson 2 covers locale-aware type changes for exactly this reason.

The pipeline is a loop, not a line

05

Power BI Demonstration

Here is the pipeline expressed as what you actually click in Power BI Desktop, end to end. You will do all of this properly in Lesson 5; for now, just see the shape.

Get Data
Home › Get data › Text/CSV. Choose the file. Power BI shows a preview and two buttons: Load and Transform Data.
Choose Transform Data, not Load
This single habit prevents most beginner problems. Load takes the data as-is, including whatever types Power BI guessed. Transform Data opens Power Query so you can look before you commit.
Inspect and clean in Power Query
Turn on View › Column quality, Column distribution and Column profile. Fix types, remove columns you do not need, handle nulls. Every action becomes a step in Applied Steps on the right.
Close & Apply
Home › Close & Apply. Power Query runs the whole recipe and loads the result into the model. You are now out of Power Query and back in Desktop.
Build relationships in Model view
Click the Model icon in the left rail. Drag from a dimension's key column to the fact's matching column. Check the cardinality and direction Power BI chose.
Write measures
In the Data pane, right-click your fact table › New measure. Write the DAX. Set its format in the Measure tools ribbon that appears.
Build visuals in Report view
Click the Report icon. Pick a visual from the Visualizations pane, then drag fields into its wells.
Format and arrange
Use the Format pane (the paintbrush icon) to control everything about the visual's appearance. Arrange the page with alignment tools under the Format ribbon tab.
Save and publish
File › Save as to write the .pbix. Then Home › Publish to push it to a workspace in the Power BI service.
HomeGet dataText/CSVTransform DataClose & Apply
The single most valuable habit in this lesson
Always click Transform Data, never Load. Even when the file looks clean. It costs you five seconds and it means you have seen the data before it enters your model. Practically every horror story in Power BI starts with somebody clicking Load.
06

Example Dataset

This lesson is conceptual and needs no download — it uses the sample data shown inline above. The next lesson that requires a file will link it here.

You can still browse every dataset in the course from the dataset library.

07

Step-by-Step Exercise

A diagnostic drill. For each symptom below, decide which pipeline stage is the most likely cause, and what you would check first. Write your answers before reading section 08.

  1. Your revenue card shows $0 when you slice by Category, but the correct total when unsliced.
  2. Total revenue is exactly right, but total orders is roughly double what the business expects.
  3. A date slicer offers 2024 and 2025 but also 1899 and 2049.
  4. Your year-over-year measure returns blank for every month.
  5. The report takes 40 seconds to load one page, and the others are instant.
  6. Revenue is 12% lower than the finance team's figure, consistently, every month.
How to approach it
For each one, ask: at which stage could this first have gone wrong? Then ask what single check would confirm or eliminate that stage. You are practising the backwards walk from section 02.
08

Expected Result

Compare your answers with these. The reasoning matters more than the exact wording.

Diagnostic answers
SymptomLikely stageFirst check
$0 when sliced by CategoryRelationshipsIs there a relationship between the product dimension and the fact table, and is it active? A missing or inactive relationship means the slice filters nothing on the fact side and returns blank.
Orders roughly doubleDAX / MeasuresAre you using COUNTROWS on a line-level fact table instead of DISTINCTCOUNT(OrderID)? Line-level facts have several rows per order.
Date slicer shows 1899 and 2049Power Query or ModelEither a bad date value survived cleaning, or auto date/time generated a range from an outlier. Check the min and max of the date column in Power Query first.
YoY blank everywhereModel — the date tableIs there a date table, is it marked as a date table, and is it related to the fact? Time intelligence functions need all three.
One page takes 40 secondsOptimisationRun Performance Analyzer on that page. One visual is almost certainly dominating — often a table with too many columns or a measure that materialises a large intermediate table.
Consistently 12% below financeGet Data or Power QueryA definition difference, not a bug. Are you summing NetSales while finance sums gross? Are returns or a channel excluded? Compare definitions before debugging code.
The last one is the most important
A consistent percentage difference is almost never a technical fault — it is two different definitions of the same word. The fix is a conversation, not code. Analysts who reach for the DAX first can lose days to this.
09

Common Mistakes

Clicking Load instead of Transform Data
The data goes straight into the model with whatever types Power BI inferred from the first 200 rows. Text columns that should be numbers, dates parsed in the wrong locale, and nulls you never saw.

Fix: Always Transform Data. Look at column quality before you load anything. It is five seconds and it is the highest-value habit in Power BI.
Building visuals before the model is right
It is more fun, so people do it. Then a relationship changes and every visual built on the old shape has to be rebuilt.

Fix: Get the model right and validate two or three base measures against the raw data first. Then build visuals. The order feels slower and finishes faster.
Fixing symptoms at the wrong stage
A number is too high, so you add a filter to the visual to bring it down. The real cause was duplicate rows from a bad merge, and now the fault is hidden behind a workaround that will break the next time someone changes a filter.

Fix: Walk backwards through the pipeline until you find the earliest stage where the data is already wrong. Fix it there. A filter on a visual is almost never the right fix for a data problem.
Never showing anyone until it is finished
You spend two weeks building exactly what was described, then discover the description was not what was wanted. This is the single most common way BI projects waste time.

Fix: Show something rough on day two or three. An ugly page with the right number on it generates more useful feedback than a beautiful one with the wrong number.
Treating the pipeline as one-directional
Assuming that once you have moved past Power Query you should not go back. Then you contort a DAX measure into something unreadable to compensate for a column you should have shaped upstream.

Fix: If a calculation is fighting you in DAX, ask whether the problem belongs in Power Query. It very often does. Going back a stage is normal.
10

Professional Tip

Check the row count at every stage boundary
Write down the source row count before you start. After Close & Apply, check the loaded row count in Data view. After building relationships, check that a simple COUNTROWS(Sales) measure still returns that same number.

Three checks, thirty seconds, and they catch the majority of silent data-loss bugs — the kind that produce a plausible-looking wrong number rather than an obvious error. Nothing else in this course gives you as much protection for as little effort.

Two more habits from working practice:

  • Rename your Applied Steps. Power Query names them Changed Type1, Filtered Rows2. Renaming to Remove cancelled orders turns your query into documentation. Future you, or whoever inherits the file, will be grateful.
  • Keep a validation page. A hidden report page with raw totals — row count, sum of the main measure, min and max date — takes ten minutes and lets you confirm at a glance that a refresh has not silently broken something.
11

Knowledge Check

Knowledge Check

5 questions. Answer each one to see why the right answer is right.

A revenue measure returns the correct grand total but blank whenever it is sliced by product category. Which stage should you investigate first?

Why does the pipeline order matter, rather than being a suggestion?

Your model loads cleanly, row counts match the source file, and your measure is a simple SUM.

Your revenue figure is consistently 12% below the finance team's number, every single month. What is the most likely explanation?

Which habit prevents the largest share of silent data problems in Power BI?

You are three days into a project and the model is not finished. Should you show the stakeholder what you have?

12

Practical Challenge

Practical Challenge

Write a pipeline failure playbook

You are about to hand a Power BI project over to a colleague who is competent but new to the tool. Write them a one-page playbook: for each of the six stage groups below, give the single check they should run and the single most likely fault they will hit.

Requirements
  • Cover all six groups: Get Data · Power Query · Model & Relationships · DAX · Visuals · Publish
  • For each, give one concrete check — something they can actually do, not "make sure it's right"
  • For each, name the most likely single fault at that stage
  • Add one line at the top explaining how to use the playbook when something is wrong
  • Keep it to one page — a playbook nobody reads is worthless

Good checks share three properties: they are specific (a named action, not a principle), fast (under a minute), and they produce a binary answer (it either matches or it does not).

Weak: "Check the data loaded correctly."
Strong: "In Data view, check the row count in the status bar matches the source file's row count minus one for the header."

For the top line, think about the direction of travel. When a number is wrong, do you start at the visual or at the source?

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

A model playbook

How to use this
When a number is wrong, walk backwards — start at the visual and move up the pipeline until you find the earliest stage where the data is already wrong. Fix it there, not where you noticed it. Do not skip a stage because it "can't be that one".
StageThe check (under a minute)Most likely fault
Get DataIn Data view, compare the row count in the status bar against the source file's row count. Also check the column count.You clicked Load instead of Transform Data, so types were guessed and never reviewed.
Power QueryIn Power Query, turn on Column quality. Look for the Error and Empty percentages on every column. Then compare the final step's row count to the first step's.A type change failed on a locale-formatted number or date, producing errors that were silently dropped or converted to null.
Model & RelationshipsIn Model view, confirm every relationship is a solid line (active), one-to-many, with the arrow pointing from dimension to fact. Then put a dimension column and a fact measure in one table visual and confirm the numbers vary by row.A relationship is missing, inactive, or set to the wrong cardinality — producing correct totals but blank or repeated slices.
DAXDrop each base measure onto a card with no filters and compare it against a manual aggregation of the source file in Excel.COUNTROWS used where DISTINCTCOUNT was needed, or a measure written against a line-level fact as if it were order-level.
VisualsOpen the Filters pane and read every filter at visual, page and report level. Then check the visual's field wells for an unintended aggregation (Sum where Average was meant).A leftover filter from testing, or a numeric column defaulting to Sum when it should be Average or should not be aggregated at all.
PublishOpen the report in the service as a different user if possible. Check the semantic model's refresh history for failures.Credentials were not configured after publishing, so scheduled refresh has been failing silently and everyone is reading stale data.
The line worth adding at the bottom
"If the difference is a consistent percentage every period, stop debugging and go and compare definitions with whoever produced the other number. It is almost certainly not a technical fault."

This one line will save your colleague more time than the rest of the playbook combined.
14

Lesson Summary

Key takeaways
  • There is one pipeline behind every Power BI project: sources → get data → Power Query → clean → model → relationships → DAX → measures → visuals → report → interaction → optimisation → publish → decision.
  • The order is a real dependency chain, not a convention. Each stage consumes the previous stage's output.
  • It groups into three phases: Prepare (slow, unglamorous, load-bearing), Model (architectural, expensive to reverse), Deliver (fast to iterate, highly visible).
  • When something is wrong, walk backwards through the stages. The fault is usually one stage earlier than where you noticed it.
  • Always click Transform Data, never Load. It is the highest-value habit in the whole tool.
  • Check the row count at every stage boundary. Thirty seconds of checking catches most silent data-loss bugs.
  • A consistent percentage difference is a definition mismatch, not a bug. Go and talk to someone.

Syllabus topics delivered

The complete analytics workflowEach pipeline stage explainedWhere projects usually fail
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 4% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.