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.
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 …
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 ?
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.
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 🙂
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!
Check out the community blog page where you can find valuable learning material from community and product team members!