Power BI Dataflows - Simply Explained

Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Your power BI reports keep redoing the same clean up a data flow could do once every refresh reruns the same steps, every report keeps its own copy, and that duplication slows things down and hammers your source system. Welcome to the M365 FM podcast, where we explore the people, ideas, and technologies shaping the future of work. You can prepare that data once in the cloud, then reuse it everywhere. By the end, you'll know where your data actually sits. When a data flow is worth the effort, and which of the two versions. To learn the messy data problem everyone hits. First, start with the thing you've probably already lived through. Sales numbers arrive in a spreadsheet, and the spreadsheet is a mess. Store names are spelled three different ways. Half the dates sit in one format and half in another, and someone left a trailing space on every single row. So you clean it, you trim the spaces, you fix the dates, and you merge the store names into one. Twenty minutes later, a colleague opens the same file and does the exact same cleanup, except their version comes out slightly different. Now the finance report and the sales report disagree about the same week. And nobody can explain why. This is the text that sits underneath a lot of reporting work. Every report carries its own copy of the clean up logic. So the same steps run over and over again. Your source system gets hit by people pulling the same rows, and every fix has to be repeated in five different places. The real tension here is that none of this is hard work. Trimming a space isn't difficult. Doing it four hundred times across four different files is what wears people down. And the moment the numbers disagree, you lose the room. What makes it worse is that the cycle never actually stops. New data keeps arriving, new reports keep getting built, and every one of them starts that clean up from zero. Nobody sets out to duplicate work, and it happens anyway because the clean version of the data doesn't live anywhere. Shared. Now imagine the cleaning happened once in one place in the cloud and every report picked up the finished result. That's the problem a data flow is built to solve, and once you see it that way, the whole thing stops feeling technical. What a data flow actually is. A data flow is a cloud service that gets data from a source, cleans and shapes it, and saves the result as a table you can reuse. That's the whole idea in a single sentence. The work runs on Microsoft servers rather than your laptop, so your machine can be switched off while it happens. The tool doing the cleaning is Power Query, and you've probably already met it. It's the same engine behind the Get and Transform button in Excel and the transform step in power BI desktop. If you can click through those menus and fix a column, you already hold the skill a dataflow needs with no new language and no coding required. Every dataflow does three things in the same order. It connects to a source, which could be a database, a spreadsheet, a web service, or a folder full of files. Then it transforms the data, which is a fancy word for cleaning and reshaping it the way you tidy a messy sheet. Finally, it loads the result into storage as a clean table that's ready for anything to use. Think about how that plays out in practice. Say you pull a sales file every Monday and it always needs the same treatment. You clean the spaces, split one messy date column into two clean ones, and rename a few headers. You save those steps as a dataflow, put it on a Monday morning schedule, and from then on the clean table just appears without you touching a single menu. Again, it can reach more than eighty different kinds of sources, which matters because real companies keep their data in more places than they'd like to admit. One data flow can pull from the finance database, a sales spreadsheet, and a web service, and leave you with a single clean table. One practical detail is worth knowing about if your data lives on a company server instead of in the cloud, you need something called an on premises gateway to carry it up. That gateway is a small bridge installed inside your network, and your IT team usually sets it up once and then forgets it exists. The best part for a beginner is that there's nothing to install and no server to babysit. You open your browser, build the steps once, and the cloud does the rest. Whether or not your laptop is even awake where your data actually sits. Beginners rarely ask one question, and it's the one that makes everything click. Once a data flow cleans your data, the clean table has to live somewhere. It goes to cloud storage, and for power BI data flows, that storage is Azure Data Lake Storage Gen2. Try not to let the name scare you. A data lake is a big cloud filing cabinet built to hold lots of data cheaply, and it's a very different animal from a spreadsheet. You don't open it in Excel and scroll around, you point tools at it, and they pull what they need. The cheapness is the point, and it's worth understanding why the data lands as plain files rather than inside an expensive database, which is exactly why a data lake can hold millions of rows without a big bill attached. That design choice is what makes this whole approach practical for ordinary teams. There are two ways the storage gets set up. Most of the time Microsoft manages it for you and you never think about it again. In larger organizations, someone points power BI at the company's own Azure Storage account, which gives the company more control and lets other Azure services read the very same files. The data is saved in a shared open layout called the Common Data Model. That long name hides something simple. The tables sit in a predictable structure, so a power BI report, a power app, and an automation can all read the same clean data without any of them rebuilding it from scratch. This is the part that surprises people most because the result lives in shared cloud storage. It isn't locked inside power BI at all. Your clean sales table can feed a report, an app, and an automated flow at the same time. Because you built it once and a whole set of tools can share it. The key thing to hold on to is that the data isn't sitting in your report file. It lives in the cloud, in a shared place, which is what makes reuse possible in the first place. How data flows fit with data sets and reports. Now let's clear up the confusion that trips up almost every beginner, which is how a data flow differs from a data set. The two words get used loosely, and that's where the muddle starts. An assembly line is the cleanest way to picture the split. Raw data enters at one end. The data flow does the cleaning and shaping the data set decides how the tables relate and what they calculate, and the report turns it all into something a human can read. Each station has exactly one job and that separation is the whole point. Consider a concrete version. Say your company sells through stores and three teams need the same sales numbers. Finance wants a monthly rollup. Store managers want daily totals, and leadership wants a dashboard. Without a data flow. Each team builds its own clean up from scratch, and the three versions drift apart within a month with a data flow. The cleanup happens once all three teams point their own data sets at the same clean sales table, and each one builds the visuals it needs on top of it. When the store names need fixing again, you fix them in one place and all three reports pick up the change. A second benefit hides in there. Each data set still refreshes on its own schedule, so the three reports stay independent, and a change to one of them can't accidentally break the other two. You get shared data without shared fragility. The payoff here is bigger than saving time. When everyone builds on the same clean table, they're all arguing about the same numbers. And that ends a lot of needless arguments in meetings. Your source system also gets touched once per refresh instead of once per report, which keeps the people who own that system on your side. One honest note is worth making before we move on. A data flow isn't a report, and it isn't a data set either. You still build a data set and a report on top of it, and the data flow simply handles the messy part that used to be copied everywhere. Once the idea makes sense, though, there's one practical catch that decides whether you can actually use it. And that catch is the license. the licence and capacity part in plain terms. Now for the part. Almost every beginner gets wrong and I did promise to come back to it. The good news comes first and it's real. A data flow doesn't need a special license of its own, because the ability to create and run one is bundled with licenses you might already have, like power BI power apps or dynamics three hundred sixty five Customer Insights. So if you're sitting on a power BI license, you can create a basic data flow. Today in power BI Dataflows work on pro on premium per user and on premium. That last part is where the fine print lives, because a handful of the best features are premium only. Let me name those features in plain words, since the official list reads like a contract linking data flows together so one builds on, another needs premium. So does doing the heavy calculations inside the data flow storage instead of in your report. The same goes for refreshing just the new rows and for the speed up engine that makes large jobs finish faster. A second layer catches people even harder. The newer fabric version of data flows needs a fabric capacity, a fabric trial, or a power BI premium capacity. A Plain Pro license won't open that door, which surprises anyone who assumes newer always means available. The word capacity scares people, so let's make it concrete. Capacity is just a pool of computing power that a company buys and shares across its fabric and power BI workloads. It's a bit like renting a bigger kitchen so more cooks can work at once, and the price reflects how big a kitchen you need. The honest version of the rule is simpler than the docs make it sound. A data flow on a basic license is real and useful, and plenty of teams run on exactly that. The moment you want the heavy lifting, you're in capacity, territory, and capacity is a conversation with whoever holds the budget. The encouraging part is that you don't need to own capacity to learn any of this. A free trial gives you a temporary capacity, which is plenty of time to build a first data flow and see how it feels before anyone spends a dollar. When a data flow is worth it and when it isn't. With the license picture clear, one question is left, and it's the one that decides whether you ever build one. For situations tell you a data flow will earn its place and they're easy to spot once you know them. The first is reuse. If more than one report needs the same clean table. Build it once in a dataflow and let them share it. The second is protecting your source. If a dozen reports each pull their own copy of the same rows, the source system feels every one of those pulls, and a single dataflow refresh replaces the lot. The third is agreement. When you want one central version of a table that everyone treats as the truth, a dataflow gives you one place to define it. The fourth is people because a dataflow is made for self-service. So someone comfortable in Power Query but not a professional engineer can build and own it. That last point matters since it lets the person closest to the business shape the data. Now consider the other side, because a dataflow isn't always the answer. If a single person uses a single report, adding one gives you an extra layer and an extra schedule for no real gain. Just clean the data inside the report and move on. A middle case is worth calling out because it's where teams get stuck. You might have two reports that share a couple of columns, but not much else. Reuse only pays off when the shared part is the expensive part. The heavy cleaning, the slow join, the messy source. If the shared work is trivial, a dataflow just adds a hop. A simple test before you build one is to ask whether two people would be annoyed if the clean table changed. If the answer is yes, the table really is shared and the data flow is probably a good fit. If you're really building an enterprise data warehouse with deep history tracking and heavy audit requirements, a data flow can carry more than people expect, but a proper warehouse is usually the better home for that job. And if you only need the data to look right on one screen one time, a data flow is overkill. One fair trade off is worth naming. Every data flow you add is another thing that can fail and another refresh schedule someone has to watch. The reuse has to be worth that overhead. When three reports share one clean table, it clearly is. And when nobody shares anything, it clearly isn't the traps that catch people out. If you decide a data flow is worth it, a few traps are waiting, and knowing them in advance saves you a bad afternoon. The first one hits beginners immediately. Data flows aren't available in the personal my workspace area, so they have to live in a shared workspace. Hunt for the button in your own private sandbox and you won't find it, which makes the feature look missing. The second trap is about ownership. Only the owner of a data flow can edit it, so if that person changes roles or leaves the company, editing stops until someone takes ownership. Taking ownership can also break the saved connections to your data sources, which is fixable, but fiddly, and it tends to surface at the worst possible time. The third trap is the one that actually hurts. Deleted data flows can't be recovered, and there's no recycle bin and no undo. You can back up the definition as a file and restore from that. But if you never set the backup up, a wrong click is permanent. The fourth trap is a refresh surprise if you change the shape of the data, like adding or renaming a column, the stored data gets wiped until the next refresh runs. The same thing happens if you switch a data flow between refreshing only new rows and refreshing everything. Both moves drop the data and neither one warns you loudly. The fifth trap is about time limits, and they depend on your capacity on a shared non-premium capacity. A single table gets two hours to refresh and a whole data flow gets three. On premium, a data flow gets twenty four hours, which is a very different world. A further rule says refreshing more than ten data flows across different workspaces needs premium. The six strap is quieter and it catches people who change a data flow after reports already depend on it. If you rename a column or add one, the reports built on top don't see the change on their own. You have to open the report in power BI desktop, refresh it there and republish it. Or the refresh fails and the report looks broken. One more trap catches people on the per user premium license. Specifically, data refreshed in that environment isn't visible to anyone without the same license, and it gets cleared when it moves out. If your team is mixed, that one surprises everybody the first time. None of these are reasons to avoid data flows. There are reasons to set one up with a little care, a backup file, and a plan for who owns it. When people move on the two generations and which one to learn. One more thing is worth knowing and it changes what you should spend your time learning. Microsoft now has two versions of data flows living side by side, and they aren't the same product. The original is now called Dataflow Gen one, and Microsoft has labeled it legacy. Legacy is a specific word in Microsoft's world. It means the feature still works and still gets supported, but it won't receive new features for customers with fabric access. Microsoft points to the newer one Dataflow Gen two as the recommended path. Gen two adds some real things. It can send your clean data to many more places, including a fabric lakehouse, a fabric warehouse, an Azure SQL database, or even snowflake. It saves your work automatically as you go. It plugs into pipelines, which are Microsoft's way of chaining steps together, and it comes with a copilot assistant that can apply transformations from a plain language prompt. The good news for anyone already invested in Gen one is that moving isn't a rebuild from nothing. You can export your queries and import them into Gen two, or copy them across in Power Query. So the work you've already done carries over. It's a migration, not a fresh start. There's a catch that most summaries leave out. Gen two isn't a strict upgrade of everything in Gen one. One thing Gen one can do that Gen two can't is connect live to a data flow through the dataflow connector without importing a copy of the data. If that live connection is central to how your reports work today, then swapping to Gen two isn't straightforward. That leaves the practical question of what a beginner should actually learn. If your team is on fabric or premium. Learn Gen two because that's where Microsoft is putting its effort. If you're on a plane Pro license, Gen one is what you have, and it still works fine for the core job of cleaning data once and reusing it. If you're honestly not sure which one your organization has, that's a fair question to bring to whoever manages your power BI licenses. The answer decides what you should practice on this week, what to take away. And one question for you. So let's pull the threads together. A dataflow cleans and shapes your data once in the cloud, so every report can reuse the same clean table instead of rebuilding it from scratch. It sits between your raw source and your report. It can feed apps and automations as well as reports. And it's real limits are mostly about licenses, capacity, and ownership. Microsoft is also clearly steering new work toward the newer fabric generation. Here's my question for you. Think about the reporting your team does today, and count how many times the same cleanup gets done by different people in different files? If the answer is more than once, you already know exactly where a data flow would help if this made the topic feel less intimidating. Subscribe to M365 FM to master the tools and the mindset shaping the future of work.