Dataflow in Dataverse [How to Create & Use]

While working on Power Apps projects, I often had to move data from various sources, such as SharePoint lists, Excel files, and other systems, into Dataverse. Doing this manually every time was impractical, especially since the data needed to stay up to date. This is where Dataverse Dataflows became really useful.

In this tutorial, we will discuss everything about Dataflow in Dataverse, including how to create Dataflow from various data sources such as SharePoint lists and Excel.

Apart from that, I explained all the data types in Dataflow and incremental refresh in Dataverse dataflow with examples.

Dataflow in Dataverse

• Dataflows are a cloud-based tool for preparing your own data. They help you import data, clean it, and load it into Power BI, Microsoft Dataverse, or Azure Data Lake Storage.
• Dataflows are included with many Microsoft products, so in most cases, you don’t need a separate license just to use them.
• You can access dataflows from Power Apps, Power BI, and Dynamics 365 Customer Insights. When you create and save a dataflow, it is stored in the cloud and runs from there.
• Dataflows are built using Power Query, the same tool used in Excel and Power BI to connect and prepare data.
• Data stays up to date because you can run dataflows automatically on a schedule or trigger them manually whenever needed.

The image above shows the basic structure of a dataflow.

A dataflow pulls data from different data sources. Currently, it supports more than 80 types of data sources. Once the data is connected, the dataflow engine processes it and applies the transformations defined using Power Query.

After that, the transformed data is loaded into the selected destination. This can be Azure Data Lake Storage, a Microsoft Power Platform environment (Dataverse), or a Power BI workspace.

Create Dataflow in Power Apps

To create a dataflow in Power Apps or Dataverse, follow these five steps:

1. Choose where the final data will be stored.
2. Select the source from which the data will be taken.
3. Apply data transformations using Power Query.
4. Set up when the dataflow should run.
5. Use the loaded data from the final destination.

Create Power Apps Dataflow From a SharePoint List

Let’s create Power Apps Dataflow from a SharePoint List step by step:

1. On Power Apps, click More from the left navigation -> Dataflows.

2. Under the + New dataflow, there will be two options:
   • Start from blank = Select this option if you want to start the Power Apps dataflow from scratch.
   • Import template = Choose this if you want to establish a dataflow using any imported template from your system.

Here, I have selected the + Start from blank option.

3. In the New Dataflow page, provide a unique name (Dataflow for Book Details) to the new dataflow and then click on the Create button.

4. Next, you’ll be asked to choose the data source from which you want to get the data. As shown in the image below, there are many available connectors. You can upload a file from your computer or select an existing connection.
   • The connectors are organized into tabs like All categories, File, Database, and more, making it easy to find what you need. You can also search for a connector by name using the search box.
   • In this example, we’ll select SharePoint List because the data will be pulled from a SharePoint list.

5. The two different connection configurations are as follows. like as:
   • Connection settings: With this configuration, there is just one field, i.e.
     • Site URL = Specify the URL of the SharePoint site that contains the SharePoint list. You can type a URL like “https://<tenant>.sharepoint.com/sites/PowerApps/“.
   • Connection credentials: There are four fields in this setting: (The choices below are available if this is your first time signing in).
     • Connection = Choose between a new connection and an existing connection.
     • Connection name = This will automatically fill in using the Site URL box.
     • Data gateway = This is selected with none by default.
     • Authentication kind = Choose the Organizational account option for the authentication type.

You won’t see the above alternatives if you have already logged in before. You only have access to the Edit connection button instead.

NOTES:

-> If you aren’t already logged in, click the Sign-in option and enter your Microsoft login information to access the SharePoint Online list.
-> Make sure the Organizational account is selected in the Anonymous section.

Click the Next button to continue when the connection is completed.

IMPORTANT:

If you type the wrong URL, username, or password, you will get the warning “Invalid credentials.” If the wrong source is picked, the error could also happen.

Refer to the screenshot below.

6. You must select the data or SharePoint list that appears in the left menu on the next page. The Power Query editor will display after we select the SharePoint list (Book Purchase Info), loading all columns and data. Click the Transform data button.

7. On this page, you have access to all feasible modifications. The designer is similar to Power BI, as you’ll see. Let’s imagine you want to update this page such that you may delete a row or a column.
   • Additionally, as you can see, the bottom portion includes details about the loading time, the number of columns (39), and the quantity of data rows (7, excluding headers and column names), after which select Next.

8. We will now map the tables, the columns, and the load configurations. You may either load it into an existing table or make a brand-new one. I’ve decided to add it to a new table. The Table name and Table display name will automatically populate this field. The field for the Table description is optional.

The table’s primary key (or unique column in SQL) must then be determined. If you already have a column, you may utilize it or leave it as Auto-generated. In my case, I’ll only use the Auto-generated default option.

On the current page, each Destination column type appears as a Text type. The entire mapping screen will appear as follows. Tap the Next button to continue.

9. Once you click the Next button, nothing will happen to take you to the next page. Under the Column Mapping section, a warning error notice with the text “Some column names only different by letter case. Please update the column names to be unique case-insensitive names.“

This warning indicates that the column name in the source is not unique, irrespective of the case used. To proceed with the procedure, we must ensure the column names are unique.

To resolve this problem, we will use the bottom-left Back button to return to the Query editor page.

10. On the Power Query editor page, you may see a column attribute with the same name i.e. ID. As seen below, one ID is lowercase, and the other is uppercase. To turn it into a unique characteristic, we must remove one ID field.

NOTE:

You may eliminate any unnecessary fields or other properties with the same name in the query editor, not just the ID column.

11. Under the Remove columns part of the command bar, choose the Id (lower case) column and click Remove columns.

A specific Id column has now been removed from the query editor. Click Next after it is finished.

12. On the following screen, we will once more see the Map table page. Simply modify the Table name before clicking the Next button.

13. Finally, Refresh manually and Refresh automatically are the two choices available on the Refresh settings page. Either manually or automatically refreshing must be chosen. If the data migration is a one-time occurrence, don’t do anything in this section.

You must choose a time interval if you choose the Refresh automatically option.

Here, choose Refresh automatically, and you’ll see two parts:

1. Frequent-based refresh = If you want the dataflow to be refreshed once a day, once a week, once a month, once a year, or more frequently, select this option. We have chosen the option where the dataflow will update once weekly for a set amount of time.
2. Refresh on specific days and times = Select this if you want the dataflow to be updated on a specific day and time.

Additionally, choose the “Send refresh failure notification to the dataflow owner” option if you wish to notify the dataflow owner of a refresh failure.

Select Publish -> Publish now, after that, and wait a bit.

14. The data flow will refresh, and the progress status will show up after some time. The last confirmation notice, “Your dataflow Dataflow For Book Details” was published successfully and is presently refreshing,” will then appear at the top of the page.

15. Now, to view the created Dataverse dataflow in Power Apps, go to Tables -> Custom tab -> You can view the SharePoint list (Book Purchase Info) name as shown below.

This way, we can create a Dataverse Dataflow from a SharePoint List.

Create a Power Apps Dataflow From an Excel

Next, we will see how to create a Dataverse dataflow from an Excel spreadsheet. Follow the steps below:

1. Create a new dataflow using + New dataflow option.

2. A Power Query window will open where you can choose your data source. Select the source from which you want to get the data. The data can come from either on-premises or cloud sources, such as Excel, SharePoint, Azure SQL Database, Salesforce, Oracle, and many others.
   • The Excel workbook will serve as the data source in this example. Select the Excel workbook under the All categories section below.

3. The next step is to create a connection to your data source; in this example, we will connect to the OneDrive Excel file.
   • Choose the Link to file option -> click on the Browse OneDrive button -> select the specific Excel file (Sales) from the OneDrive account -> click on the Select button as shown below.

4. When the upload of the Excel file is completed, the connection settings screen will show up as seen in the following image. If you want to alter the connection, use the Edit connection link button and make the necessary changes. Click on Next.

5. The tables from your Excel file will be displayed. Click Transform Data after selecting the table from which you need to transform the data.

6. You will then be sent to the data flow editor, where you may customize your data. To see and alter the code generated by the query editor, use the Advanced editor in the example below. Click on the Next button.

7. You will now be prompted to map your data; you may load it into an existing table, map it to a new table, or choose Do not load.

We’ll decide to import the data into a new table in our scenario. You are ready to proceed to the next phase after completing your choices.

Here, we just changed the Table name (SALES Table) and Table display name (SALES Table) and then clicked on the Next button.

8. The next step is to choose the dataflow’s refresh frequency. To keep data current, dataflows use a data refresh method.

You may choose a timetable for the data refresh and decide whether to refresh manually or automatically.

Here, we have chosen Refresh automatically and provided the frequency-based refresh timings as shown below. Once everything is done, click on the Publish button.

The Publish button displays two options, i.e., Publish now and Publish later. We may publish the Dataverse dataflow in Power Apps in accordance with it.

9. Our dataflow is now refreshing and is viewable in progress in the Dataflows section. After a while, you could see that the dataflow has been published and that the next refresh date and time have been displayed.

You may view the table (SALES Table) to which we just mapped the data by navigating to the Dataverse tables (Custom tab).

The dataverse table will show all the information that was taken from the Excel spreadsheet as soon as we click on it.

That’s how we can create the Power Apps Dataflow from Excel.

Various Data Types in Dataverse Dataflows

Dataverse dataflows support the following data types:

All Data Types	Dataflow Data Types
Time	Time
Date	Date
DateTime	DateTime
DateTimeZone	DateTimeOffset
Logical	Boolean
Text	String
Any	String
Currency	Decimal
Int8	Int64
Int16	Int64
Int32	Int64
Int64	Int64
Double	Double
Percentage	Double
Single	Double
Decimal	Double
Number	Double
Duration	Not Supported
Binary	Not Supported
Function	Not Supported
Table	Not Supported
List	Not Supported
Record	Not Supported
Type	Not Supported
Action	Not Supported
None	Not Supported
Null	Not Supported

Dataverse Dataflow Incremental Refresh

A dataflow can only load data from a certain time frame due to incremental refresh. This can decrease the quantity of data processed during each dataflow refresh, resulting in quicker, more dependable, and resource-efficient subsequent refreshes.

Dataverse dataflow incremental refresh includes two types. Such as:

1. Full refresh = The Dataverse table is completely refreshed with this refresh option.
2. Incremental refresh = To filter and update it for the entire period, select a certain date or time column.

To workaround with the dataflow incremental refresh, Expand Dataverse -> Dataflows -> My Dataflows -> click on ellipses of a specific dataflow -> select Edit incremental refresh as shown in the screenshot below.

Next, select Incremental refresh -> Choose a Date or Time column to filter by -> Provide the number of days, months, quarters, or years that you want to refresh the rows from the past -> Click on Save.

Dataverse Dataflow Limitations

1. Deleted data sources still appear on the dataflow data source page.
2. This does not affect dataflow refresh or updates.
3. Deleted data sources also remain visible in the Gateway dropdown.
4. Dataflows can connect to other dataflows, with a maximum depth of 32.
5. Refreshing more than 10 dataflows across workspaces requires Power BI Premium.
6. Global variables cannot be used as URL parameters in Power BI dataflows.
7. Parameters can be used, but changing them requires editing the entire dataflow.
8. Dataflow parameters work like fixed values.
9. Power Query Online limits are listed on the usage limits page.
10. Multi-Geo is not supported unless you use your own Azure Data Lake Gen2 storage.
11. VNets are supported using a gateway.

I hope this article has been helpful for learning everything about Dataflow in Power Apps, including how to create a dataflow using a SharePoint list or an Excel spreadsheet, step by step.

You may like some more Power Apps Dataverse tutorials:

• Power Apps Dataverse Yes/No Field
• Create Dataverse File Field
• Filter Power Apps Gallery By Dataverse Choice Column
• Detect Text in Dataverse Using AI Builder
• Search in a Power Apps Gallery