Process CSV files easily with Power Automate and Dataflows

[@ShihChiWang]

I've studied Power Platform related info for two years but don't know Dataflows yet. I usually use Power Query in Excel to transform data. This is really useful. Thank you.

[@thiagopais5785]

It's the same language.

[@bellcore1]

This guy is something else. Every video of his I watch saves me days of work. Absolute genius.

[@bluric0]

Wish I found this video 5 months ago! I got as far as creating my CSV files in Sharepoint, but the next steps of converting those files to a Sharepoint List or something else like this is exactly what I need to complete my end to end automation workflow, saving us a total of 18 hours every quarter. Will test the steps out tomorrow and give it a go, thank you!

[@stevenhampson8657]

Thank you Paul for this detailed walk through of the whole process for the use of dataflows. I keep coming back to this video as a reference. Appreciate the great work here and also on your own channel. One question - how does one use environment variables for a dataflow trigger (when refreshed) as the actual id of the dataflow will change and it can't be determined before the trigger step.

[@charlesseguin23]

I look forward to the day when Power Query is integrated directly into Power Automate. We could then process data within the flow, and output directly to the flow, without the need to use Dataverse as a staging area. Also keep in my that, contrary to Flows, dataflows cannot run concurrently; that is a problem if you need to process many CSV files, through triggers that happen around the same time. Finally, ALM for dataflows needs to be improved. The dataflow connector for Power Automate does not have the handy "current environment" reference that the Dataverse connector does. Also, dataflow connections are different from Power Automate connections and cannot be handled through the solution. Dataflows are getting better and better... but there is still much to do to allow them to reach their full potential.

[@wyattdavid]

Excellent video, using power query in power automate is going to unlock loads of opportunities that were just too complicated before

[@gregoirepesenti]

do you know how To Get dataflow Rows Count BEFORE the "list rows" action, and then dynamically change "Row count" is list row, because the 5000 rows by default, makes my create item in error, because the loop try top patch empty item, I can figure out how to count dataflow rows ... thanks !

[@ericxls93]

Very good Paul! Thank you. Please make a video on how to use Power automate to refresh an excel power query?

[@bobleach]

Great video, I didn't know that Power Query was available to be used with PA, just a shame that it appears that a Premium licence is required to get the data back out from Data verse. Is there any way to get Power Query data flow working online with PA but not store it within Data Verse so that I can just use standard connectors?

[@nigelsmith2165]

You can get data out of Dataverse for free if you use Dataverse for Teams. It supports all of this video's transformations.

[@bobleach]

@@nigelsmith2165 Thanks, but I don't understand how you would get the data back out of Dataverse for Teams - Would you not still require the Power Apps/Automate premium licence?

[@umairahmad1752]

Is there a way to read a csv file from share point online using power automate?

[@theopneustos3712]

Has the interface changed? When I go to power apps, the menu on the left has a link to "Flows" and I can't open up PowerQuery. I'm talking about at 4:13 in the video. I can create a Dataflow template, but I can't open Power Query.

[@ellenwubbels2603]

On the left I see Tables, Flows, Solutions and More. Clicking on more showed the Dataflows option

[@anveshm5397]

@@ellenwubbels2603 you are awesome. Thanks for that. I had the same question as OP

[@TwentyNailsBike]

Thanks so much Paul. Any ideas why my table is not updating records deleted from the source file (CSV)? Already checked the "Delete rows that no longer exist in the query output" is checked when exporting data to the table and the primary key is created. Thx!!

[@BuddhaAfterDark]

great tutorial but, how do i save a .csv as a .xlsx? if anyone can do that simply in Power Automate please let me know...

[@RedCloudServices]

Paul thank you. Our govt agency is using Enterprise Power Apps including Dataverse. We have a few Master tables which must be periodically updated with a fresh cvs file. Can Power Automate bring in an updated csv file into an existing Dataverse table?

[@gwilympugh139]

My powerapp is different to this. Has it been updated since? I can't access the data source from dataflows. The only options are to create cloud flows or desktop flows and it's all very different?

[@Watever44]

Is it possible to have a dataflow with a dynamic input filename ? so we could call it from power automate by passing the ID of what we want to convert ? It would be easier if PowerAutomate could transform CSV files into XLSX or something else with a function. It's something a lot of people are trying to automate with multiple step around.

[@anandasanyal]

Thank you for sharing. It's indeed a nice technique. But as you know, there is a limitation of Dataflow (5000 rows) and if I'm getting 50,000 rows in CSV, How to handle the import? What will you advice in that case? BR

[@ramizmollah3685]

Thanks for the great video

[@GrEgO169]

Flows are premium api through power apps application :(

[@theownmages]

It's ALM supported for this? Can i just add my dataflow to a solution and import it as managed to another environment and expect the power automate in that same solution to still work?

[@tristangeoffroy9286]

I don’t think so. I haven’t seen dataflow as an existing object you can list and impor into your solution. I had to recreate it in each environment.

[@theownmages]

@@tristangeoffroy9286 the fact that Microsoft promotes solutions that can't be properly integrated into an ALM pipeline is awful. My users are actually following these guides. Then they get mad at me when I say "i can't push this to production", it's gonna be like hell to manage

[@tristangeoffroy9286]

@@theownmages agree any improvement from Microsoft would be highly appreciated and will certainly comes soon

[@gregoryoliveira8358]

Hi. Thanks for sharing this content with us. What function do you use to transform the attachment into a csv file? I need to save a csv file from a e-mail into a SharePoint folder and I am failling.

[@cPathz]

Which function were you referring to?

[@gregoryoliveira8358]

@@cPathz I used the base64ToString.

[@joshcone6971]

This looks cool, however whenever I try to publish I just get "publishing has failed something went wrong" :(

[@joshcone6971]

Also, part of your flow is that a file is created in onedrive after being emailed to you. Won't that create another file called "sessions.csv" which would be renamed to sessions_1.csv or something? Wouldn't dataflow ignore that file?

[@vicarion]

@@joshcone6971 Create File can choose to overwrite any existing file and keep using the same file name. It might have failed because you don't have a dataverse license, that's why this failed for me.

[@cPathz]

@@vicarion I dont have one :(

[@Pops_Tech_Tutorials]

I wish I had the "dataflows" option.... 😢

[@doodlebug1820]

Thanks but The data goes into dataverse or azure both of which have issues with cost and complexity, ie i have to convince a bunch of people to shell out money and learn entirely new system. Learning pq is easy but learning dataverse or azure is not, just figuring out the licensing is a project in itself. Dataverse also you cant make a new table each time you have to reuse an existing table so it gets weitd esp if columns are dynamic each run. Also the video shows that you had to re type in the column headers, as far as i can tell you cant easily get original column headers out of dataverse.

[@DanielFerrara-s7n]

Thanks so much for wasting all of our time. Why is it so easy to open a file and save it as an xlsx but Power Automate wants to put out these boring videos?

[@anveshm5397]

You are talking about one file but if you have multiple files then what would you do. The automation is needed right!