Dataflows in the power platform can be used for easy data integrations to your Dataverse database. Today we’ll be going over what dataflows are, and how you can take advantage of them.
What are Dataflows?
Dataflows are a fairly new feature to Microsoft’s Power Platforms set of tools. There are technically two types of Dataflows: Analytical and Standard. We will be covering Standard Dataflows, as Analytical Dataflows are mainly used for Power BI visualizations.
Standard Dataflows are built in two steps: gathering and transforming your data through Power Query, and then mapping that data to a Dataverse table. Power Query is a tool originating with Power BI that is used to pull in data from multiple data sources and transform it to meet your needs. Once the data is transformed using this tool, you can then map those columns to fields in a Dataverse table.
Once a Standard Dataflow is built, it can then be put on a “Refresh” schedule – Refresh meaning executing the dataflow and syncing the data.
How to get started
For the type of Dataflow I’m describing here, you build them at make.powerapps.com. From there, on the left navigation under “Dataverse”, there is an option for “Dataflows”. There you will see a list of your existing dataflows and be able to create new ones. For Microsoft D365 CRM users, you can also pass Dataflows between environments using Solutions (by adding them to a solution after you have built them here).
One thing that might catch you by surprise is there is very little control over Inserts vs Updates. Since the destination system is a Dataverse database, you have to pick a Key on the entity that will uniquely identify the record (not to be confused with the record’s GUID). If the Dataflow finds a record with the matching Key, it will do an update; otherwise, it will insert the record. That’s the only control you have over Inserts vs Updates. You can however do some fancy queries within the Power Query editor to assist in controlling this; for example, you can query the destination system to see if the record already exists manually, and choose to do only inserts or only updates.
Another limitation that you might have picked up on already – it’s destination system must be a Dataverse database. You can’t sync data from your Power Query to any system. It would have been nice if it worked similarly to Power Automate where we can take advantage of the hundreds of Connectors available – but that is not the case here.
There’s also some issues with Sharing and Ownership of Dataflows. I wish there were better logging options and a way for other users to view results. Currently, there is only a checkbox stating “send failure reports to the Dataflow owner” and no way to share them with other users (as far as I know, as of 10/10/2022).
With those limitations in mind, Dataflows are very capable of accomplishing a lot of data integration needs. I think it’s great that Microsoft was able to utilize a tool like Power Query and create an extremely useful feature out of it.
Sources, Destination and Scheduling
There are quite a few data sources available to Dataflows, though not quite as expansive as Power Platforms Connectors; but you have your important ones available: Dataverse, databases, Excel files, Text/CSV files, Sharepoint, etc. You can even chain Dataflows together and use a Dataflow as a source.
As far as the destination goes, it’s only the currently selected Dataverse environment. Wherever you are building the Dataflows, it’s that environment that is the destination system.
Scheduling can be whatever schedule you would like – every 15 minutes, daily, weekly; whatever your needs may be. You can also choose to skip the schedule part of it, and manually execute them instead – and these can be executed from other Power Platform tools such as Power Automate or Power Apps. As with most Power Platform products, there are limits on how many times you can run them, how long they are allowed to run, etc. That mostly depends on your licensing.
Wrapping up, Dataflows are an extremely useful feature that almost entirely gets rid of the need of custom data integrations to CRM. You can easily set up a new CRM instance using your existing data by utilizing Dataflows; and integrating systems together is easier than ever.
You can find more information about Dataflows from Microsoft’s official documentation here: https://learn.microsoft.com/en-us/power-query/dataflows/overview-dataflows-across-power-platform-dynamics-365