Self-Service Data Preparation in Power BI - Stop Rebuilding the Same Queries in Every Model

Open five Power BI files from five different analysts in the same organisation and I'd bet good money you'll find the same Power Query steps copied into most of them. Same connection to the ERP, same filter to remove test customers, same twenty-step query that turns the general ledger into something readable. And each version is slightly different, because one analyst fixed a bug in March and the other four didn't.

That's the problem Microsoft's self-service data preparation usage scenario is trying to solve. The idea is simple: do the data preparation once, in a dataflow, and let everyone who builds semantic models reuse that output instead of rebuilding it.

We've written before about dataflows themselves. This post is more about the usage scenario: who does what, how teams should be organised, and where it tends to fall apart.

The core idea

In this scenario there are two kinds of creators.

Data creators build dataflows. They connect to source systems, clean and shape the data in Power Query Online, and publish tables that other people can use. These are usually your stronger technical analysts, the ones who know which ERP table actually holds the right invoice date.

Semantic model creators connect to those dataflows instead of the raw sources. They focus on relationships, DAX measures and the model design, then build reports or hand the model to report creators.

The point is that the hard, fiddly data prep work happens once, and the result is shared. When the logic changes, it changes in one place.

This sits between pure self-service (everyone does everything themselves) and enterprise BI (a central team does everything). It's a sensible middle ground for a lot of Australian mid-market organisations, the 200 to 2,000 staff range, where there are capable analysts in the business but not a big central data engineering team.

Why it works

The benefits are real and we've seen them on plenty of projects.

Consistency. If finance and operations both pull customer data from the same dataflow, they at least start from the same customer list. That doesn't guarantee their reports match, but it removes one of the most common reasons they don't.

Less load on source systems. Instead of fifteen semantic models each hitting the ERP every morning, one dataflow hits it and the models read from the dataflow. We had a manufacturing client whose on-premises ERP was groaning every morning at 6am because of Power BI refreshes. Moving the extraction into a few shared dataflows cut the query load on that server dramatically, and the ERP team stopped sending angry emails.

Skills specialisation. Not every analyst needs to understand the quirks of the source system. The person who knows that the TRANS_DT column is in UTC but POST_DT is in local time does that work once. Everyone else gets a clean date column.

Credentials in fewer places. Source system credentials and gateway connections live with the dataflows, not scattered across dozens of personal semantic models.

Gen1, Gen2 and where your data lands

This is where things have shifted over the last couple of years. The original Power BI dataflows (now called Gen1) store their output in Power BI managed storage, or optionally in your own Azure Data Lake Storage Gen2 account. Semantic models connect to them with the dataflows connector.

Dataflow Gen2 in Microsoft Fabric works differently. It still uses Power Query Online, but it writes output to a destination you choose: a lakehouse, a warehouse, an Azure SQL database and so on. That makes the prepared data available to more than just Power BI. Your data scientists can read it from a notebook, and SQL users can query it directly.

My honest view: if you're starting fresh in late 2026 and you have Fabric capacity, use Dataflow Gen2 with a lakehouse destination. The self-service data prep pattern still holds, but the output becomes more broadly useful and it lines up with where Microsoft is putting its investment. If you have a big estate of Gen1 dataflows that are working fine, there's no need to panic. Plan the migration, but don't drop everything to do it.

One caution. Dataflow Gen2 consumes Fabric capacity units, and a badly designed dataflow can eat a surprising amount. We've seen a single dataflow with a few unnecessary merges against large tables use more capacity than the rest of a client's workload combined. Keep an eye on the Capacity Metrics app in the first few weeks.

Where it falls apart

The concept is easy. Making it last is harder. These are the problems we see most.

Nobody owns the dataflows

A dataflow that twelve models depend on is now critical infrastructure. If it was built by an analyst who has since moved to a different role, and nobody else understands it, you have a single point of failure dressed up as self-service. Every shared dataflow needs a named owner and at least a paragraph of documentation about what it does and where its data comes from.

Too many dataflows, not enough sharing

Ironically, some organisations end up with dataflow sprawl. Each team builds its own dataflow for customers, so instead of five copies of the Power Query logic in five semantic models, you have five copies in five dataflows. The problem has just moved.

Fixing this is mostly organisational. You need a place where people can find existing dataflows (endorsement and the OneLake catalogue help), and a culture of checking before building. Promoting or certifying the canonical customer dataflow makes it obvious which one people should be using.

Heavy lifting in the wrong tool

Power Query is great for a lot of things. It's not the right tool for everything. When a dataflow is doing complex joins across millions of rows, slowly changing dimension logic, or anything that takes more than about half an hour to refresh, it's usually a sign that the work belongs in a proper data engineering pipeline: a notebook, a Data Factory pipeline, or SQL in a warehouse.

Self-service data prep works best for medium-complexity shaping that business analysts can understand and maintain. Once it starts needing a data engineer to keep it running, it's no longer really self-service, and you should treat it accordingly. Our Data Factory consultants spend a fair bit of time helping clients move the heavy stuff out of dataflows into pipelines that are built for it.

Refresh chains nobody understands

Dataflow refreshes, then the semantic model refreshes. If the model refreshes before the dataflow finishes, it picks up yesterday's data. This sounds basic, but it's one of the most common causes of "why are my numbers wrong this morning" tickets we see.

Fix it with orchestration rather than by guessing at refresh times. In Fabric you can use a pipeline to refresh the dataflow, then trigger the semantic model refresh once it succeeds. That's a lot more reliable than scheduling the dataflow at 5am and the model at 6am and hoping.

Gateways become the bottleneck

On-premises data sources need a gateway, and when lots of dataflows go through one gateway at the same time in the early morning, it struggles. Use a gateway cluster, spread refresh schedules out, and size the gateway servers properly. This is boring infrastructure work, and it's frequently the actual reason a self-service programme feels slow.

Governance that doesn't kill self-service

The temptation once you see these problems is to lock everything down and have the central team build all dataflows. Sometimes that's right for the most critical data. But if you go too far, you've rebuilt enterprise BI with a slower request queue.

What's worked for our clients:

• Let skilled analysts create dataflows in their own team workspaces freely
• Promote the good ones, and certify the few that become organisation-wide sources
• Require certified dataflows to have an owner, documentation and a deployment process
• Run a short community of practice session every month or so where data creators show what they've built

That last one sounds soft, but it's often the thing that stops five teams building the same dataflow. People reuse what they know about.

The AI angle

We're increasingly seeing clients ask about putting AI over their data, whether that's Copilot in Power BI, Fabric data agents, or custom agents that query business data. Shared, well-named, clean dataflow outputs make that much easier. An AI agent querying a tidy Customers table in a lakehouse with sensible column names will give far better answers than one picking through raw ERP tables with columns named CUST_FLD_07.

So the work you put into self-service data prep pays off twice. Once for your human analysts, and again when you start building AI on top. If that's on your roadmap, our Microsoft Fabric consultants can help design the data layer with both uses in mind.

Should you adopt this scenario?

If you have several analysts building Power BI models from the same sources, yes. Start small: pick the one source everyone uses (often the ERP or the CRM), build one good dataflow for the core tables, get two or three model creators to switch to it, and fix the problems that come up. Then expand.

What I wouldn't do is announce a big "data prep programme" and try to move everything at once. It's the boring, incremental approach that actually sticks. If you want a second opinion on where to start, our Power BI consultants are happy to have a look at what you've got.