Power Query: Document the Why, Not Just the What

TL;DR

Before “fixing” something, check the history (tickets, emails, comments)
Every weird filter exists for a reason someone forgot to document
A 10-second comment saves 2 hours of rework
The client doesn’t remember what they asked for 3 weeks ago

The Story

“We need the North and South branches to appear in the dashboard.”

Easy, I think. I go to the model, look for the branches… and they don’t exist.

I check the database. They’re there, with all their data.

I check Power Query. And I find this:

= Table.SelectRows(
    Source,
    each (
        [branch_name] <> "NORTH BRANCH" and
        [branch_name] <> "SOUTH BRANCH"
    )
)

Someone had explicitly filtered them out. No comment. No documentation. No context.

The Dangerous Reflex

My first instinct: remove the filter and move on.

But something told me to check the ticket history first. And there it was, three weeks earlier:

“Please remove those two weird branches from the report, I don’t know what they are.”

The same client. The same person.

If I had removed the filter without checking, I would have undone work that someone did for a reason. And when the client complained again about “those weird branches,” we’d be in an infinite loop.

Why This Happens

1. The Model Accumulates Decisions

A Power BI model that’s been in production for months has dozens of implicit decisions:

Filters that exclude “garbage” data
Columns renamed to make sense
Transformations that correct source errors
Exclusions by explicit user request

Each one made sense when it was done. None are documented.

2. People Rotate

The analyst who made the filter left. The consultant who built the model finished the project. The intern who “fixed a bug” is now a senior somewhere else.

The knowledge left with them. The model stayed.

3. The Client Forgets

It’s not malicious. The client manages 50 projects, 200 daily emails, and 10 meetings. Your dashboard is one of many things on their mind.

When they say “fix it,” they genuinely don’t remember asking for the opposite a month ago.

How to Document in Power Query

Comments in Steps

Every Power Query step can have a comment. Use it.

// EXCLUDED: North and South branches per client request
// Ticket #1234 - 2025-12-15
// Contact: John Smith ([email protected])
= Table.SelectRows(
    Source,
    each (
        [branch_name] <> "NORTH BRANCH" and
        [branch_name] <> "SOUTH BRANCH"
    )
)

Descriptive Step Names

Instead of Step1, Step2, use names that explain:

Bad	Good
Filtered Rows	Exclude_Test_Branches
Removed Columns	Remove_Legacy_Columns
Changed Type	Convert_Dates_ISO

Documentation Step

Add an initial step that does nothing but documents:

// ============================================
// MODEL DOCUMENTATION
// ============================================
// Author: Your name
// Last modified: 2025-12-30
//
// IMPORTANT DECISIONS:
// - North/South branches excluded (ticket #1234)
// - Sales < 0 treated as returns
// - Customers without email marked as "N/A"
// ============================================

let
    Source = ...

Checklist Before Modifying

Before “fixing” something that looks like an error:

1. Is There History?

Check related tickets/issues
Search emails with the client about this topic
Look at commits/previous versions if there’s version control

2. Are There Comments?

Check comments in the Power Query step
Search for project documentation
Check notes in the report itself (if any)

3. Can You Ask?

Contact the original author (if available)
Ask the client before acting
Consult with the team

4. If There’s Nothing…

Document your decision NOW
Create a ticket/record of the change
Notify the client of what you’re going to do

Common Patterns: “Errors That Aren’t Errors”

Test Data Exclusion

// Looks like error: why are we excluding these customers?
= Table.SelectRows(Source, each [CustomerID] <> 0 and [CustomerID] <> 99999)

Reality: They’re test IDs the system uses internally.

”Arbitrary” Date Filter

// Looks like error: why only from 2020?
= Table.SelectRows(Source, each [Date] >= #date(2020, 1, 1))

Reality: Before 2020, the data was in another system and isn’t comparable.

Removed Columns

// Looks like error: why remove the email?
= Table.RemoveColumns(Source, {"Email", "Phone"})

Reality: GDPR. The client doesn’t have permission for that data to appear in the dashboard.

Replaced Values

// Looks like error: why are all negatives zero?
= Table.ReplaceValue(Source, each [Sales] < 0, 0, ...)

Reality: Returns are processed separately. Here we only want gross sales.

What to Document (Minimum)

Element	Example
What	”Exclude North and South branches”
Why	”Client request - not real branches, test codes”
When	”2025-12-15”
Who requested it	”John Smith, ticket #1234”
Who did it	”Jane Doe”

A one-line comment is better than nothing:

// North/South branches excluded per client request (ticket #1234, Dec 2025)

The Cost of Not Documenting

Without Documentation	With Documentation
2h searching for why there’s a filter	10 seconds reading the comment
Undoing work that was correct	Understanding it was correct
Confused client	Satisfied client
Infinite loop of changes	Informed decision

Documentation isn’t bureaucracy. It’s saving yourself future work.

Conclusion

Every weird filter, every strange exclusion, every non-obvious transformation… probably exists for a reason.

Before touching:

Search the history
Read the comments
Ask if in doubt

And when you make changes: document the why, not just the what.

10 seconds writing a comment today = 2 hours saved in 3 months. If the numbers still don’t add up after checking the history, my Power BI debugging guide walks through the systematic approach to finding the real issue. And if you’re dealing with inactive relationships in your model, that’s a whole other rabbit hole worth exploring.