The Complete Analytics Workflow
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.
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.
- 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
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:
- Is the visual filtered by something you forgot? Check the Filters pane.
- Is the measure wrong? Test it in a card with no filters at all.
- Is the relationship dropping rows? Check for blank keys on the fact side.
- Did Power Query filter rows out? Compare the loaded row count to the source.
- Is the source itself incomplete? Check the file.
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.
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.
| Phase | Stages | Character | How long it takes |
|---|---|---|---|
| Prepare | Sources → Get Data → Power Query → Clean | Detective work. Slow, unglamorous, full of judgement calls. Nobody sees this work but everything depends on it. | Typically the largest single block of project time |
| Model | Semantic model → Relationships → DAX → Measures | Architecture. Decisions here are expensive to reverse later, so think before you build. | Substantial, and worth not rushing |
| Deliver | Visuals → Report → Interaction → Optimisation → Publish → Decision | Communication and craft. Fast to iterate, and where stakeholder feedback lands. | Feels like most of the work; usually is not |
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 | Produces | How you verify it |
|---|---|---|
| Get Data | A connection and a preview | Row and column counts match the source |
| Power Query | Clean, typed, correctly-shaped tables | Column quality shows no unexpected errors or nulls; row count is explained |
| Semantic model | Fact and dimension tables | Every table has a clear role; no orphan tables |
| Relationships | A working filter path | Slicing a fact measure by a dimension column returns sensible, varying numbers |
| DAX / Measures | Business definitions | Each base measure matches a manual aggregation of the source |
| Visuals | Encoded numbers | The chart type matches the question; totals match the measure |
| Report | A readable page | Someone unfamiliar can state the headline in 30 seconds |
| Interaction | Follow-up capability | Every slicer and drill actually does something useful |
| Optimisation | Acceptable speed | Performance Analyzer shows no visual dominating the load |
| Publish | Shared, refreshing content | Someone else can open it and the refresh succeeds |
| Decision | A changed action | Someone tells you what they did differently |
Where the stages map onto this course
| Stages | Module |
|---|---|
| Data sources, Get Data | Module 2 — Connecting to Data |
| Power Query, Clean & Shape | Module 3 — Power Query |
| Semantic model, Relationships | Module 4 — Data Modeling |
| DAX | Module 5 — DAX Fundamentals |
| Measures | Module 6 — DAX for Real Analysis |
| Visualizations | Module 7 — Visualization |
| Report design | Module 8 — Dashboard Design |
| Interaction | Module 9 — Interactive Reports |
| Optimisation | Module 11 — Advanced Power BI |
| Publish & share | Module 12 — Power BI Service |
| Decision | Modules 10 and 14 — Projects, Portfolio & Career |
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.
| Stage | What the value looks like | What 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 Query | 1249.5 | Type changed to Decimal Number with correct locale |
| Model | 1249.5 in Sales[NetSales] | Stored in a compressed column |
| DAX | SUM(Sales[NetSales]) = 6,311,491.41 | Aggregated across 15,067 rows |
| Visual | $6.31M | Formatted, and placed in context by a trend line |
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
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.
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.
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.
- Your revenue card shows
$0when you slice by Category, but the correct total when unsliced. - Total revenue is exactly right, but total orders is roughly double what the business expects.
- A date slicer offers 2024 and 2025 but also 1899 and 2049.
- Your year-over-year measure returns blank for every month.
- The report takes 40 seconds to load one page, and the others are instant.
- Revenue is 12% lower than the finance team's figure, consistently, every month.
Expected Result
Compare your answers with these. The reasoning matters more than the exact wording.
| Symptom | Likely stage | First check |
|---|---|---|
| $0 when sliced by Category | Relationships | Is 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 double | DAX / Measures | Are 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 2049 | Power Query or Model | Either 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 everywhere | Model — the date table | Is 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 seconds | Optimisation | Run 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 finance | Get Data or Power Query | A definition difference, not a bug. Are you summing NetSales while finance sums gross? Are returns or a channel excluded? Compare definitions before debugging code. |
Common Mistakes
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.
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.
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.
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.
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.
Professional Tip
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 toRemove cancelled ordersturns 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.
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 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?
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.
- 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?
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
| Stage | The check (under a minute) | Most likely fault |
|---|---|---|
| Get Data | In 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 Query | In 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 & Relationships | In 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. |
| DAX | Drop 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. |
| Visuals | Open 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. |
| Publish | Open 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. |
This one line will save your colleague more time than the rest of the playbook combined.
Lesson Summary
- 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
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.