Dealing with data in Power Automate simply isn't as easy as it should be. You are left to deal with data about looping over it, which is not only clunky but can burn through Power Platform API calls, leaving you and your flows throttled.
There is Office Scripts, which can be incredibly powerful (they are one of my favourite Power Automate features, if you want to know more check out my blog here), yet they have their own drawbacks:
- Require developer to know TypeScript and Office Script language
- Has to be managed outside of the Platform
- Not built for ALM (moving between accounts is a nightmare)
But there is a hidden gem that fixes all your data problems, Dataflows. What's surprising is not many developers know about them or use them.
Dataflows are Power Query in Power Automate. Just like how you can transform your data when importing into Power BI, Dataflows are the same. So what's great is:
- Anyone with Power BI/Power Query can pick it up
- Its internal to platform
- Works with ALM
Create a Simple Dataflow
Strangely Dataflows aren't made in Power Automate, but Power Apps, under the Dataverse options.
The Dataflow has to be built first, before it can be called in a flow (like an AI builder model).
As said, it's just Power Query, so anyone with Power BI experience will pick it up straight away.
On a new Dataflow first select your data source, it can be pretty much any data connection, in this demo Im going to use a text/csv file (a notoriously difficult file to deal with in Power Automate) hosted in SharePoint.
I want to do the following data transformation
- Remove top row
- Remove Columns
- Reorder Columns
- Set Column type
- Add Calculated Column
- Filter Data
- Sort Data
All of this is easy in a Dataflow.
Becomes:
You just step through all the changes you want and they are recorded in the 'Applied Steps' list in bottom right corner. Here they can also be edited and rearranged (I'm not going into detail how to do these steps, but it's very easy to pick up and there are links at the end of the blog).
The next step is where to store the transformed data, this needs to be a custom Dataverse Table.
The fields need to be mapped to the table field, and the Dataflow can be setup to either append to the table or overwrite all data.
Run In a Flow
To run the Dataflow in a flow just use the Dataflow Refresh a dataflow action. The Dataflow is linked to the file/data, so the flow owner doesn't need access to the data, just the Dataflow.
Below Im going to just save it as a new csv, but you can do anything you can with data in Power Automate.
Then you either set up another flow (recommended) or set long delay (not recommended), to download the Dataverse table.
The When a dataflow refresh completes trigger is ideal as will trigger on every Dataflow refresh.
And that's it, easy to set up, and easy to maintain. And there is so much more you can do, Pivot/UnPivot data, find and replace, the list goes on and on (just think how powerful Power BI is with data). Dataflows really should get more coverage because they are so useful.
Further Reading
Top comments (2)
Hi David, Greetings!
Great article and Just would like to understand is there any way out to hide the dataflow option from power apps (make.powerapps.com) for user based on security role? If so what is the role you would suggest to pick?
Thank you for your time.
Hi Thangaraj,
Thank you for the kind words. Currently the only way is the dlp policy, though this just blocks use of data flows, not the creation of them. I know Microsofts new Power Automate UI plans to hide connections if not available in the dlp policy, fingers crossed they hide dataflows if not allowed too