Showing results for 
Search instead for 
Did you mean: 
Advocate III
Advocate III

Looking for a way to transform CSV files and upload to CDS



I work for a college.  Our students take exams in 3 different applications.  We get the students results by downloading csv files from the applications (web based apps).  Of course, the 3 csv files don't have the same format.  We want to upload all the results in a standard entity in our CDS environment.




  • I created a solution in PowerApps. (By the way, I don’t use any model nor canvas apps.  Only some dataflows created in a solution and uploading data in the local CDS database)
  • I created 3 dataflows that get a csv file as input and uses Power Query steps to transform it to fit our standard entity for test results.
  • It works fine but there’s a lot of manual interaction with very low value added SO I’m thinking it a good scenario for Power Automate


Eventually I want to use UI Flow to automate the download but I’m not there yet… to be continued.

What I’m really having trouble with is to automate the transformations currently done with Power Query.

I want to create a Flow that triggers when a csv file with the students results in drop into a folder in Teams (SharePoint)


I tried …


  • I thought I could call the dataflows I created in PowerApps from a Flow triggered when a file is created in SharePoint -> FAILED !
  • Since I can’t use my dataflows can I at least export and reuse the M code I did?  I found that is it ONLY possible with the SQL connector. ☹ FAILED !
  • I was very happy to learn that CDS has a new SQL endpoint… I tried for days but I never was able to connect to CDS with the SQL connector available in Power Automated (note that the endpoint works fine when I use SSMS to connect to it.) FAILED !


The ugly solutions …


I’m thinking about using Excel as a Power Query engine.  The first triggered Flow would open Excel, and with a VBA macro (I guess) it would run a Power Query to transform the file and save a new output file into another SharePoint … which would triggered a second Flow that would read this file line by line and use the CDS connector to insert one row at the time into the CDS entity.  I heard there’s some kind of Scripting for Excel online too… that might be an other option… but still ugly!


OR, I can use a bunch of variables, arrays and buildin sting fonctions to replicated the transformation I did with Power Query directly in Flow... can I ?


I already lost a week and a half on this attemps at Power Automate and I’m starting to hate that stuff! ☹  I think I need support from the community!  I need suggestions to enlight me!


So I’m asking this question:  

How would YOU automate the transformation of 3 different cvs files and upload them in an entity in your organisation’s CDS environement ?


Thank you! 


Hello molegris,


I can't say that I have ever tested this in my environment, but maybe one of these articles can help you out.  If not, it's possible someone else in the community may have a better idea.



Hi Ross,


Thanks for your reply.  I found some part on the answer in the first link you suggested.  However, I was forced to go with the "ugly" solution of using hudge fx_expressions to transform the data.  I'm looking forward to a better integration of Power Query 🙂



Helpful resources

MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (2,775)