If Power BI cleans names, stitches reference lists and rebuilds logic that does not exist in the source on every refresh, the report becomes fragile. It works until something changes in the source. Part of that work belongs in the data model and preparation rules, and part of it belongs back where the errors appear.
What BI that "treats" the sources looks like
An illustrative but very familiar example. A report on service recipients is built from a register. Power Query (the data preparation editor in Power BI) has several dozen steps: trim leading spaces from names, turn "Kyiv city", "Kyiv" and "KYIV" into one value, pull region codes from a separate file, filter out test records by the word "test" in the surname.
The report shows nice numbers. But look at the signs:
- Dozens of value replacement steps. Every refresh fixes the same errors again, because nobody fixed them in the source.
- A mapping table in Excel. The "what it was, what it should be" table sits next to the report, often named something like
mapping_final_v3.xlsx. It has no owner apart from the report's author. - The same cleaning in several reports. Each analyst writes their own version, slightly differently. One source, two numbers in the meeting.
- Indicator definitions live in DAX (the Power BI formula language). Who counts as a recipient, which status is active, which date we count on: a measure in the report decides that, not an agreed rule.
- The refresh breaks on Monday morning because someone renamed a column in the source or added a new spelling.
If you recognise two or three of these, the report rests on one file and one person.
Why this is fragile
It is like a stain on the ceiling from a leaking roof. You can repaint it every month and the ceiling will look tidy. But the roof still leaks, and the paint costs you every time.
The analogy has limits: everyone can see a stain on the ceiling, while a fix in Power Query is visible only to the file's author. And every room is painted with its own paint, meaning every report cleans the data its own way.
That leads to four practical consequences:
- The error in the source lives forever. The people who enter the data never learn they are entering it wrong.
- Different reports give different numbers, because their cleaning differs slightly.
- Knowledge sits with one person. A holiday or a resignation, and the report turns into a black box.
- Refresh becomes slow and unpredictable, because heavy preparation runs every time in every report.
What to keep in Power Query and what to move out
You do not need to move everything. One question helps: does anyone other than this report use this rule?
Keep in BI:
- formatting, labels, category order for visuals;
- filters that concern only this report;
- light reshaping of a table for a specific chart;
- measures that describe display, not the definition of an indicator.
Move into the data model and preparation layer:
- normalisation of values: names, case, date and number formats;
- mapping tables, in a table with an owner rather than in someone's file;
- finding and handling duplicates;
- indicator definitions: who is a recipient, which status is active, which cut-off date;
- joining data from different systems.
Send back to the source:
- errors that repeat with every load. They should be fixed where data is entered: a rule in the form, a reference list instead of free text, a check at entry.
An illustrative example of preparation layers
The scheme below is not tied to a specific tool. It can be a database, Power BI dataflows or a separate warehouse. The order matters more than the technology.
- Raw layer. A copy of the source data as it is, with the load date. Nothing is fixed here, so you can always check what arrived.
- Cleaned layer. Correct types, formats and normalised values. Mapping tables connect here, and each one has an owner.
- Business logic layer. Indicator definitions and the tables reports are built from: who is a recipient, which statuses, which cut-off date. One definition for all reports.
- BI. Visuals, filters and measures on top of a ready model. The report displays, it does not repair.
Separately from the layers, keep a list of errors found during cleaning. It goes to the source owner, so the number of fixes shrinks over time instead of growing.
How to start without a big rebuild
- Open Power Query in your main report and mark each step: formatting, cleaning or business logic.
- Find the steps that repeat in two or more reports. Move those first: they already cost you different numbers.
- Bring the manual mapping tables into one place and give them an owner.
- List the errors the report fixes every time and send it to the source owner.
- Whatever stays in the report should concern display only.
After a few of these steps, Business Intelligence goes back to what it is for: helping people make decisions. And the preparation rules become part of the data architecture instead of the secret of a single file.
A question for you: how many steps does your largest Power Query have, and how many of them are actually treating the source?