How to Create a Dataflow in Power BI

Building a powerful report in Power BI often starts with messy data. If every report requires you to manually clean, filter, and structure the same data sources from scratch, you're not just wasting time - you're opening the door for inconsistency. This is where Power BI dataflows come in, offering a smarter way to handle your data preparation. This article will show you exactly what dataflows are and provide a step-by-step guide to creating your first one.

What Exactly is a Power BI Dataflow?

Think of a Power BI dataflow as a shared, reusable recipe for preparing your data. Instead of performing the same data cleaning steps - like removing unwanted columns, filtering rows, or combining tables - inside every single one of your Power BI reports, you do it once in a centralized location called a dataflow. It’s essentially a collection of tables (called entities) created and managed in the Power BI Online service, not the desktop application you use for building visualizations.

The entire process of preparing the data is done using the familiar Power Query interface, but it all happens in the cloud. Once your dataflow is set up and refreshed, you and your teammates can easily connect to this pre-prepared, clean data from any Power BI Desktop report. This means everyone is working from the same clean, consistent "source of truth."

Dataflow vs. Dataset: What’s the Difference?

It’s easy to get dataflows and datasets confused, but they serve two distinct purposes:

• A Dataflow is all about data preparation and transformation (ETL). Its job is to ingest raw data from various sources (like Excel files, SharePoint lists, or a SQL database), apply cleaning steps, and store the resulting clean tables in the cloud (specifically, Azure Data Lake Storage Gen2).
• A Dataset is what powers your reports and dashboards. It's the data model. This is where you might create relationships between tables, write DAX measures, and define calculations. Your dataset can connect directly to a dataflow as its source, but it can also connect to other sources as well.

In short: you use a dataflow to do the heavy lifting of cleaning the data, and then you use a dataset in Power BI Desktop to connect to that clean data and build your visualizations.

Key Benefits of Using Power BI Dataflows

While creating a report directly in Power BI Desktop works just fine for small projects, dataflows offer compelling advantages as your reporting needs grow.

1. Reusability and Efficiency

This is the biggest win. Imagine you have a complex sales table that needs ten different transformation steps to be useful. Without a dataflow, you have to repeat those ten steps in every single sales report you create. With a dataflow, you define those steps once. Now, anyone building a sales report can simply connect to your finished, clean table. This dramatically reduces development time and enforces consistency.

2. Guaranteed Consistency

Ever had a situation where two different reports show two different "total revenue" numbers for the same period? This often happens when analysts apply slightly different business rules or filters during data preparation. Dataflows solve this by centralizing that logic. When "revenue" is defined one way in the dataflow, every report that pulls from it will use that exact same definition, ensuring everyone is on the same page.

3. Improved Performance

Dataflows offload the resource-intensive work of data transformation from your personal computer to the Power BI service. Refreshing complex queries can slow down Power BI Desktop. By letting the dataflow handle this work in the cloud on a set schedule, your desktop application remains fast and responsive. You're working with the finished result, not waiting for the backend-processing to complete.

4. Clear Separation of Roles

Dataflows allow your team to specialize. A data analyst or IT expert can focus on creating and managing robust, reliable dataflows for the entire organization. Meanwhile, report creators - who may be less technical - can easily access and use this clean data to build insightful visualizations without ever having to worry about the complex data wrangling happening in the background.

Step-by-Step Guide: Creating a Dataflow in Power BI

Ready to build one yourself? Let’s walk through the process. To create a dataflow, you'll need a Power BI Pro or Premium license, as this feature is managed entirely in the cloud-based Power BI service.

Step 1: Access a Power BI Workspace

First, log in to your Power BI account at app.powerbi.com. Dataflows live inside "Workspaces," which are collaborative areas for creating and sharing content. You can use an existing workspace or create a new one, but you generally want to avoid creating dataflows in "My Workspace" unless it's for personal use only.

In the left-hand navigation pane, click on Workspaces and select the one you want to use.

Step 2: Create a New Dataflow

Once inside your workspace, click the + New button at the top left corner of the screen and choose Dataflow from the dropdown menu.

You’ll be presented with a few options. Since we're starting from scratch, we'll focus on the most common one. Select the card that says Add new tables.

Step 3: Connect to a Data Source in Power Query Online

Selecting "Add new tables" launches Power Query Online, which is the web version of the same Power Query Editor you use in Power BI Desktop. The interface will look very familiar.

You can now choose your data source. Let's use a popular business scenario: an Excel file hosted on SharePoint or OneDrive that contains sales information.

• Search for and select Excel workbook.
• Provide the SharePoint site URL or select 'Browse OneDrive' to connect to your file and sign in with your login credentials.
• Navigate to your file, select it, and click Next.
• The familiar Navigator window will pop up, allowing you to select the specific sheets or tables within the Excel file you want to use. Check the box next to your data and click Transform data.

Step 4: Transform and Clean Your Data

Now you're in the Power Query editor, where the magic happens. Here, you'll apply all the cleaning steps that you want to be reusable. Let's perform a few common transformations:

Remove Unnecessary Columns:

• Source files often have extra columns that aren't needed for your report (like "Notes" or "Row ID").
• Select the columns you don't need by holding down the Ctrl key and clicking each header.
• Right-click on one of the selected headers and choose Remove Columns.

Filter Out Rows:

• Let's say you only want to analyze data after a specific date.
• Click the filter arrow on your "Order Date" column.
• Go to Date Filters > After...
• Enter the date from which you want to keep the data and click OK.

Change Data Types:

• Power Query is usually smart about this, but it's always good to double-check. Ensure numerical columns (like Sales Amount and Cost) are set to "Decimal Number" and date columns are set to "Date". You can change this by clicking the little icon in the column header.

As you perform these steps, you’ll see them being added to the Applied Steps panel on the right. This is the "recipe" your dataflow will run every time it refreshes.

Step 5: Save Your Dataflow

Once you are happy with your data transformations, click the Save & close button in the bottom right corner. You will now be prompted to give your dataflow a name. Choose something descriptive that reflects a process, like "Core Sales Data Prep" or "Marketing Campaign Data," and add a description if you like.

After saving, Power BI will ask you to refresh the dataflow for the first time. This action runs through all the applied steps and loads the clean data into the cloud storage.

Step 6: Configure a Scheduled Refresh

A static dataflow isn't very helpful, you need it to stay up-to-date automatically. Back in your workspace, find your new dataflow, click the three dots (...), and choose Settings.

• Expand the Scheduled refresh section.
• Toggle it On.
• Choose your desired refresh frequency (e.g., Daily), your time zone, and add the times you want it to refresh (e.g., 3:00 AM every morning).
• Click Apply.

Your dataflow will now automatically pull the latest data from your source and run your cleaning steps on the schedule you set. You now have an automated data pipeline!

Putting It to Use: Connecting to the Dataflow in Power BI Desktop

You’ve built the dataflow. Now, let’s see the payoff. The final and most important step is to use this clean data in a Power BI report.

1. Open a new or existing Power BI Desktop file.
2. On the Home tab, click Get Data and select Power BI dataflows. (You may have to click "More..." to find it).
3. A navigator window will appear, showing all the Power BI workspaces you have access to. Expand your workspace, and you will see the dataflow you just created.
4. Select your dataflow to reveal the tables inside it. Check the box next to the table(s) you need and click Load.
5. The data will load into your report, perfectly clean and ready to use in your visuals - no additional transformation needed!

That's it. You can now build charts, tables, and KPIs with the confidence that your data is consistent, accurate, and centrally managed.

Final Thoughts

Learning to use Power BI dataflows is a game changer for aspiring data analysts and teams. By creating reusable transformation workflows, you save an incredible amount of time, improve report performance, and ensure everyone is building on a foundation of consistent and trusted data. It brings a new level of organization and scalability to your analytics process.

While Power BI makes data preparation more accessible, we know there's still a learning curve, and the time spent building and managing data workflows is time you could be spending on strategy. With Graphed, we’ve collapsed that entire process by allowing you to simply connect your data sources - like Google Analytics, Salesforce, or Shopify - and ask for the dashboards and reports you need in plain English. We handle the complex connections, preparation, and live updates automatically, so you can go from data to decisions in seconds, not hours.