Process CSV files easily with Power Automate and Dataflows

hello everyone and welcome to this power automate how to video my name is paul morana i am a microsoft business applications mvp and independent consultant i'm going to be showing you a really easy way to process csv files in microsoft power automate and really reduce the complexity of that procedure i have actually already written up a procedure how to do this which works really well for simple ish csv files manual t gomez has done the same thing with a similar technique to me so it's a well documented procedure but what a lot of these um blog posts like my own one don't cover is how to work with quite complicated csv files that need transformation or have difficult to work with elements and that can be really quite difficult to deal with in power automate and so you might end up going to use a third party tool to process those csv files so i'm going to show you a way that's actually much easier and works with very difficult to process csv files and it's using every just tools that are available natively in power platform so um we'll just get on with it let's have a look at the sample csv and i'll show you what's difficult about it um so as you can see there's a few lines at the top which don't contain any information that we want to use that's easy enough to deal with but if you look at the third column which is title some of these rows are encapsulated in double quotes and the reason for that is because they themselves contain a comma or they might contain themselves a double quote the presence of those elements makes it very difficult to deal with when you're processing line by line in power automate because this csv changes midway through the file that makes it even more difficult to deal with and this is not wrong so this is an amazon provided file and the way that they've done it is actually fine it just makes it difficult to work with here you can see some of the number figures also have got commas and double quotes and others do not so if you open this file up in excel excel would have no problem dealing with this but it is more tricky about automate so what i'm going to start with is just a really really simple flow which is just when an email arrives in my inbox if it has the subject filter amazon session data then continue the flow so this won't trigger this flow unless the subject filter contains amazon session data and so we'll say only with attachments yes and include the attachment content in here we've got a very simple apply to each action applied to each loop which will create files that represent the attachments and if we look in here here is that folder where the files will end up so i'm just going to send myself an email so that'll be coming into my email any moment and this flow will execute obviously at the moment this flow doesn't do anything particularly useful and i don't need it to do anything useful yet and because we are not really going to process this incoming csv data in power automate let's just check the run history it ran two seconds ago and the file will be turning up here shortly so instead now of processing this in here we are going to initiate a data flow but before we can do that we need to create the data flow so if i go to make.powerapps.com we can go to data flows and then we can say create a data flow and we'll start from blank what's important to point out at this point is that you could process any kind of data because what we're going to use is power query if you've used power bi this will be completely familiar to you so you could use what i'm going to demonstrate here to scrape data from so many different sources and even if you could do that in power automate if it became very complicated you might just want to get um power query to do the job for you i'm calling this power query no they call it power query here i was wondering if it was called a data flow so i'm going to choose text csv i'm going to browse my onedrive i'm going to go into my dataflow demo and there is my sessions csv it is there on the cloud so now that that is the selected file we can begin to transform it power query will generate a preview and then we can continue with our transformations so here is the preview and we can't just work with it like this because we've got no column headings and we need to work with some of these columns for example i want to remove this comma in the figures and go to some of these extra characters in the titles so we'll choose transform data and the good thing about this is that i'm no expert in power query whatsoever so if i can do it you'll easily be able to do it the first thing we need to do is get rid of these rows at the top so i'm going to say remove top rows and looks like we need to remove three rows they're gone and we need now some column headings so i am going to click use first row as headers and so now we've got our data split and notice how power query wasn't fussed about the double quotes or the quoted columns it just dealt with it and it also correctly changed the types of the fields and made them make sense straight away now i'm going to actually undo that step of it changing the types it is useful but we don't need it right now and so from here i'm going to just do a couple of things i want done to change this column name to go to the brackets and in this column i am going to remove the double quotes so i'm going to say replace values i'm going to find the double quote and replace it with nothing so if i go back one step you can see here where it said fish tank and now it says fish tank without the quotes i'm also going to remove the commas and the reason for this in this case i'm going to create a csv at the end and i don't want those things in there so the rest looks okay actually i think it did actually correctly change the types but we don't want the dollar signs in here so i'll do it to both of these columns it's now our csv which would have been hard work to create in power automate is already looking in pretty good shape and we could add custom columns in here or calculated fields and that would all work nicely the thing is with this particular csv file it has no unique identifier and we are going to need one to have power query doing the things that we want it to do your incoming data does have a unique identifier you will not need to add one so that could be like a database row id or something or guide something along those lines so i'm going to say add a column and i'm going to choose index column and i've got my index column there if i go back to the transform tab i'm going to mark this as a key column that's got a little key next to it now and this is just so that every time a new file comes in it will wipe what we had in the table before if we wanted it to and replace it with this data so now i'm sort of done there i'm going to say next so i'm going to load this to a new table i'm going to call it amazon sessions and it's going to make all of these fields for me automatically now because the way that i'm going to work this i'm also going to tick delete rows that no longer exist in the query output which means next time my file comes in it'll get processed again and if there are less rows than the previous csv then they'll just be deleted and the existing rows will all be updated so whatever's in my table will always have the correct data for the file that's just coming if you if you just want to keep adding data on and you've got a unique id you probably wouldn't click that and then you'd have a nice store of data in database so i'm doing this also in the default environment but you could do this in a teams environment as well so i'm just going to say next it's asking me for a key which i thought i'd put go back one unique primary column name is index okay so i've specified my keys now and i should be able to move on so i'm going to set this to refresh manually because this is where power automate gets involved again when the file comes in we will have power automate refresh it for us so if i press publish now this will do the first importation of this data and bring it into a table in databus ready for us to pull out and use in whichever way we want but before we get into the pulling of the data out we just let the data go in and then we'll initiate that data going in from power automate so now that our transformation is published which takes a bit of time the first time you do it we can then begin to work with this data in power automate and furthermore we can initiate this transformation in power automate whereas in this instance i've done this transformation manually and initiated it manually we are going to change the power automate flow now to say when this data file comes in initiate this transformation and then we can trigger another power automatic flow to say when this transformation is complete we'll do some more work with it okay so if i go into my run history back without super simple flow where we just saved our attachments if i edit this now choose data flow i can choose refresh a data flow and we should see our process amazon session data data flow there so if i just go to my onedrive i've got my sessions csv there that will leave it there for now because i know it'll work so now we can start a new flow at this time i can use the trigger when a data flow refresh completes so i can choose the same data flow and the reason that these two things are split which when you try this out you'll begin to understand it sometimes if the data flow is quite complicated it can take a while so it makes sense to have an initiation for the data flow and then a trigger for when it's complete based on this trigger we know when the previous step is complete so i'm just going to stick in a compose action so that i can save this and i'll just put the end time in this is really just so that we know that we can then work with it so if i now go back to my onedrive i can delete this and go back to my previous flow as you can see this one has got no runs we haven't run it when a data flow refresh and so i'm just going to rerun this and this will now not only save the attachment which we should see appear in here any moment but it will also begin that refresh so our file has arrived and the refresh has been initiated if we came back over to here we would see the last refresh date will soon update you can see that it's now in progress that refresh very shortly when a data flow refresh completes will initiate i'm just going to name this okay so now we can see that our data has in fact refreshed because this flow has been executed um 20 seconds ago so from this point forward we can use standard actions to build something else out so we can just use the standard listroads action from microsoft database and then choose our amazon sessions table and i don't need any filters on there for now and i could just select action to make it a bit more readable and so we'll just take a few fields just to make it okay so let's just check that we've got some data okay so here you can see our data has arrived and in fact i'm just going to edit it very quickly include the title that was the road that we did the most modification on okay so at this stage our data has been imported the data flow is run the refresh is complete and we can see that our data is all there ready for us to do something with so just by selecting those rows out with my select action i could actually just go to create csv and use that output that select and i would have a completely new csv just comprising of those four fields i wouldn't have to do any manipulation of the data at all inside power automate because i've done that all already in my transformation step we could also just do um add some rows to a sharepoint list so create item and this will take us straight into and apply to each when we do this which is right get those fields um and that will do let's put pageviews in as well these actually may not work because they i think then i think we imported them as number types but we'll see remember how i set the sharepoint list up and just so that we are not waiting for too long i will change the concurrency on here so that this applied to each happens a little bit quicker okay so if we go over to my sharepoint list now you can see that that data has just come straight in from that csv file and ended up straight into a sharepoint list we've also got a csv that we've just created with one action in power automate and so for example we could also do create html table and i'm going to use the output of the select so i can take that html table i just made put it straight into the output save that so now if i test that again we're going to get all of that good information but with very basic actions we're going to create a csv table add records into sharepoint create a html table there it is and send an email containing that data all with the transformations that we have applied without having to generate any complicated power automate queries so if i check my email there it is there i didn't do any formatting on it but you get the idea so that was a really quick overview of how you can simplify the input of really any data using power automate and data flows and data flows offer really massive capabilities in terms of data transformation but they do it in a way which isn't complicated to implement to do some of those things that we've just done in that data flow within power automate it's quite hard work and using a data flow makes it really simple and the transformations that you can apply to the data during its data flow phase make it so much easier to use it's not to invalidate any other method that's previously been worked with my own method has been very popular but my own method would fall down at this complication stage because it's not built for handling very complicated incoming data so i would really encourage you to have a look at power bi data flows they're not actually anything to do with power bi in this case but you could then use power bi to report on that data straight from database so it really does open up a whole load of options for being able to process incoming data using power automate both to execute the data flow and then to trigger when that data flow has been completed so that you can then move on generate reports from power automate or power bi or anything within the power platform stack so some i just would encourage you to give it a try and see how you get on and if you do get stuck you can reach out to me on twitter on the power automate community forums and i'll be sure to help you out and if not i'm sure there'll be another super user that will so i hope this was useful to you i'll see you in the next video [Music] you

Channel: Microsoft Power Platform

Views: 58,048

Keywords: PowerApps, Power Automate, Power Platform, CSV, Dataflow, Excel, Process Automation, Microsoft

Id: 8IvHxRnwJ7Q

Length: 20min 25sec (1225 seconds)

Published: Thu May 05 2022