 Hello everyone and welcome back to another video. So in today's video, we're going to start taking a look at power automate So power automate is a great tool to give it that name Available with Microsoft in the office 365 and it allows you to automate a rounded range of tasks What we're going to be covering off in the next following videos on from this one in today's video We're going to look at the scenario of being able to Take an X and a SharePoint list which you can see on my screen here and get a daily Extract of that so what it will do is every day it will go extract this list into a CSV file And it will save it to a desired Document library also on a SharePoint the reason for doing this scenario for one of many is So in our scenario here, we can see we've got a staff list and all this information is randomly generated So if it happens to coincide with any factual data, it is completely by chance just to put it out there and Because it's a staff list we might want to track this over time so we can see what our staff headcount look like over multiple months or even over years and What the benefit of obviously extracting this to a CSV file and storing it on our Document library is that's going to give us all those snapshots so we can look at that picture Historically over time and we may even build upon this same scenario to show you obviously the benefits of then how you could use that in something Like maybe a Power BI report But we can see we've got the list here what we're going to be using as our source and We've then got a test archives up here, which is simply a document library available on SharePoint So if you haven't got SharePoint admin rights, then you'll need to get your administrator to set these up for you but I've obviously got these here as our demonstrations and Not to go into much detail, but you can see our staff list has got some very basic made-up information And the idea in this scenario is obviously this is going to be maintained daily with new joiners And maybe when joiners have an end date and hence is the snapshotting extracts We're going to be getting that allows us to obviously build this picture up over time So in order to do this automation because we could simply do this manually ourselves But you know why do something manually when we can start automating? So we're going to be using the power automate So if you're using Windows 11, you should be able to access the desktop version of this But like me who's using Windows 10 nothing Pro actually we're going to be using the The web version available to us in office.com and all to do that What we're going to do is go into our more apps option at the top here and you can see amongst other things We've got the option here of power automate So it's going to click that and I'll open that up for us in another tab Which I'll just move along here and you can see for When you first open up power automate it's going to give you some other suggestions So there's many scenarios and ways you could use this such as being able to extract and save attachments from emails For just the first thing that pops into my mind But as I say we'll be looking at these other scenarios in future videos So if you haven't now's a great time to get you to subscribe to the channel So you're notified as those videos do come out. So for us what we're going to do is we're going to just create a new Flow as they call it in power automate. So to do that, I'm going to go into my flows and you'll see I've already got one Available here, but we'll ignore that and we're going to go straight into new flow hit the drop-down and you can see We've got a few options available to us. So You can have automations. So things that trigger this flow to run But what we're going to do is schedule this based on a time So I'm going to go into scheduled cloud flow and the flow name So here you just need to give you in that give it a name that obviously is going to be beneficial to what it's doing And what we'll call it is daily Download for lack of a time term. So we'll call it that and so this is when I want to run the flow So starting from and today's 20th of August was a 20th of August and I want this to run every day at Let's go for 10 p.m So again sticking with our scenario 10 p.m. Generally that list isn't going to be maintained anymore because office hours going to be closing at 5 p.m So we're going to do this for the last point available in the day to make sure that all changes have been captured and Repeat every one minute at the moment. We want that we want this to repeat every day. So we'll just go into create and You can see we've now got our first trigger point. So for us, it's going to be this it runs every day So I'm just going to go into edit and just fine-tune some of this so you can see the interval is every one day It's going to advanced options So I'm just going to make sure for clarity. We've got the time zone correct So I'm going to set this to UTC and where is it gone? I'm going to actually put it as doubling Edinburgh London time just because that's where I am But you can obviously change that to whatever is applicable for you Start time so we want this to start running from later today and at these hours So I want this to run at let's just slightly change what I did earlier So let's let this to run at 1045 and then this is just hopefully counteracting any sort of times You might have with daylight savings when time jumps or back one hour. So we're going to stick with 1045 It's just nicely at the end of the day, but we'll leave it as that So once we've done our trigger and we're happy that I'm just going to go into new step And this is where we can now start building out what our flow is actually going to do So the first thing you need to do is obviously get that extract or extract that information from our SharePoint list So in order to do so, we're going to search in this search box here And you can see there's a list of options all available everything from teams Excel Like I said before many times now in this video, there's so many different ways that you can source information So I'm just going to search in here get items because I know exactly what I'm looking for and you can see it's Given us a list of all the options we've got available in SharePoint So again, you can have a look through these but if not, don't worry We'll start covering off more more of these options in future videos. We're going to go into get items and It's obviously you're now going to try and sign us into our SharePoint site. So let me just do that Cool so we can now see we're in and you know it's in because one there no error message has popped up But also it's now given us these parameters that we need to provide So our first one we need to do is provide the site address Which I want to select from here and we can see it's the only one available in this list because I only have one SharePoint site And once that's accepted what it will do is it will pre populate this next selection So it's now given me all the lists available on that SharePoint site So I want to go down to staff list because that contains our staff list give it believe it or not and The next thing we want to then do is we have gone to advanced options But we don't need to worry about any of these again all options that you could do here if it was required for your project But no, we've told it. We want to go and get all the items from staff list So the next thing we need to do having captured those items is to create a CSV file But in order the first part is we just need to create a CSV table. So if we type in here create CSV You should or hopefully yet you can see we've got create CSV table available to us down in the options here So I'm going to select that and in this box here This could be a bit misleading, but if you collect in the box and go over to this pop-up You can see we've got some dynamic content and the very first one we've got here is list of items So this is going to be all the values or the list of items that were acquired During this previous step of get items So you can delve into that into more detail to the complexity if you wish But in the short version it simply just go into get items So I'm going to select value from there and then what we're going to do in this scenario is we're going to go into the advanced options And rather than have automatic columns So what we'll do in this scenario is it will automatically look at all the columns available to us in the SharePoint list and It'll create all of those columns for us in the CSV table Well, I only want to do a select number So I'm going to go into custom and then here is where we can now map our Values to columns so anywhere or the left side here where it says header This is going to be the column header available in the CSV file and the value is going to be the Corresponding value we want to use from the values we extract from our SharePoint list So for me, I'm going to put in here. I believe it's employee ID was the first one So we can then see actually if I scroll down it'll show me So if you scroll down here, you'll see all the available options available to you And there will be additional to the ones that you obviously know and want You'll see there's all these other different things like modified creative fields that are available in SharePoint But we're not interested in those so for me I've got the employee ID is going to be my first column header in Excel Or the CSV table to be more specific and the value I want to map to that is employee ID The next one is going to be first name, which quite obviously is going to be first name We'll then also do surname We've then also got an email Let's go email we'll go start date Which is going to be start date and we have end date. I believe it is yes So we do end date So that is all the fields I wish to utilize in the CSV table from our SharePoint list But the one additional one I want to do is also get a snapshot date. So I'm going to call this So just call it a snapshot again lack of better terms and then what I'm going to do here is use some dynamic Content or use an expression rather than a field extracted. So what I'm going to do is go into an expression here and I am going to Utilize an expression value called UTC now Which is simply just you know get the current UTC time What this will do by default is as you can see here is returns the current timestamp as a string So not only will it produce the date at which this is run But it'll also do the time as the date timestamp. I'm just going to change this all the format of this So all I need to go is format date time, I believe it is Open brackets. Yeah, we can see that's worked and I'm going to just change the formatting of this to only be date. I mean, yeah, the day the month And I've done made a mistake already instead of double quotes. We need to do single quotes here. So we've got day month and Year and what you might have also noticed there is I've done the m's for month in capital That's simply because capital m's will give us the month Whereas if you do lower case m's it will bring back the minutes at which this is run So we'll just do it. Okay, and if it didn't like it what it will do It'll give an error message saying that something is wrong, but we can see so far. It's all looking good So we've now got all of our mappings that be created in that CSV table And then the last step we want to have on here It's simply going to be create file. So let's go into create file and It will be another SharePoint one. Yeah, so we've got it here create file let's select that and Similarly to get items it now wants us to provide the location in which we want to create this file So for me, I'm going to go site address So it's gonna be the same SharePoint in Terms of the folder path. So if I click this folder icon, it's now going to give me all of the folders that are available on that SharePoint site for me I have a Test location called and I can't remember what it is now it will be test archive Here we are. So if I go into the arrow, it allows you to navigate into that folder We don't want to be in that level. So if I go back to SharePoint and scroll back to test archive I'm just gonna simply select that and then this is the folder within here that we want to save our content to So obviously this is a separate document library But ultimately that is where we want it to be saved to the file name for this So we're going to call it staff list Dot CSV so ensure that you have got that dot CSV available on there And then the last thing we're going to do in this content here is you can see it's great so When you look at this having selected in this field here You can see obviously we've got our dynamic data available to us So everything all the fields available in get items which is obviously the get items up here are listed in this section here And then the top one to the last step that we've done you can see create CSV table has given us this output So we just want to select this output because this is the content What's going to appear in this file that we're creating here and that should be all the steps We need to do so all I'm going to do now is simply save this flow and Hopefully I'll do that yet We can see that the flow is ready to go and we recommend you test it So if we just close this window here and sorry before I close that you can see it gave us some recommendations of something We could improve on but nonetheless will stick with what we've got so all I'm going to do now is go into well before that We'll go to our destination so we can see currently our test archive library is empty Hopefully we're now going to put a file in there So if we go back into our flow and we click on test So yeah, it's going to give us the only the option of manually So just click manual and then click test at the bottom here and then run flow And then hopefully now click done just to get rid of that side panel You'll see that the flow is now working So we can see it's got a green tick for the trigger green tick forget items a green tick for create CSV table And also apparently we've got a green tick here for create file. So it's saying a file has been created Hopefully in the right location So we'll now go over to our test archive document library at the moment. It looks empty So all we're going to do is quickly refresh this page and As if by magic you can see we've now got that file available to us there. So let's click into staff list and Have a look so once it opens up Perfect. So we can see we've got all of our fields available to us So all the fields that have come out of our list So let's go back into that so we can see this is our SharePoint list and then how this is how it now looks in the CSV format You could obviously delve into this a bit more and we can start tidying up these two columns here So start date and end date we can see it's in the date time format But obviously that's something you could delve into but also another important factor is you can see this dynamic column That we created for snapshot has populated today's date the 20th of April 20 of April the 20th of August against each one of the rows in this table which obviously as we start building this out and getting daily Snapshots and especially if we to pull this into a reporting tool like Power BI It gives us the ability to Identify exactly the date that we want to report on or if we look at these over multiple months It gives the ability to report by month But nonetheless, we can see that everything has report and has captured here as we intended Let me just close this file and go back to the document here So one thing we just need to change To give us the flexibility we're after is at the moment. Obviously, we just called this file staff list dot CSV Now the problem we'll have with that is when it comes to this flow work running maybe tomorrow Obviously, it's going to either try and overwrite this file and call it the same thing Or it's going to call it something like staff list one dot CSV. So nonetheless, it's not going to be very indicative of what the data is So what I want to do now is update our flow so that includes a date stamp of when this data was captured Just gives us a bit more clarity So we'll go back into our power automate tab and we'll go into the create file option here Actually, we won't do that we because it's now showing us obviously the test and it's showing us all the output What we will do indeed is go in top right here. We'll go to edit and Where it says file name we'll just we'll delete that for now Remove that out of there and instead go into add dynamic content Oh, it was already open for us there But we'll go back into that and this time we'll go into expression and we're just going to make a Dynamic way of obviously calling it staff list, but obviously including today's date So in order to do that the very first string option or function we have available is concat So we'll select that and this now allows us to connect concatenate a number of strings together And obviously build our dynamic file name So the first thing we want to do we have a single quotes because that's what we need to do for string is we'll call this staff List and also in there I'm going to include an underscore and hopefully this will then become clear as we move along The next thing I want to combine in my text string and to do the next part. I need to separate by a comma and This time I want to get today's date. So let's go into format date time so you might remember this step from previous when we are trying to save the Snapshot date within the actual file so format date time open brackets and within this function We're going to utilize UTC now I'll just type it there, but obviously you can scroll down for these options here to select what require and I want to format UTC now in this particular format. So I want to go Year Followed by the month Oh too many m's and then followed by the day and then what that will do is it will give us this string Which goes in year month day and it's just it's very easy way just to obviously archive your files But also I find it just gives an easy way when it comes to sorting the data or any other ways You need to minute like that data going forwards So at the moment what will happen? It will give us a file name of staff list underscore and the date at which this obviously is running But an additional piece we need to do here is we just need to add the dot CSV to the end of our file So the file type So let's do one more comma for our third and final part of this Catenation and in quotations will just put dot CSV Once we've done that we click okay Everything's been accepted so it can see it's looking good there and we'll click save And then wait for it to save yet. We can see it's all good. We'll lastly go back into our test We've got manually selected here and let's go on to test Yes, we're happy to run the flow and then let's go done and now we can see as it steps through the Options here. So you see everything else is done. I don't think I called off a second go But you can actually see the duration of time taken to perform this action. So zero seconds one second one second to get the items from SharePoint Zero seconds pretty much to create CSV table and it's actually quite taken seven seconds to create that file on SharePoint as well So we're happy. This is all now worked. Let's go to our test archive and without even refreshing We can see that we've now got a new file added But this time it's captured the today's date when it's run and then the benefit of this is as we move into tomorrow We'll also have in here the 21st of august 22nd august and so on and so forth And then what that will do as I mentioned earlier is when it comes to reporting Maybe we're only interested in one of these files from each month But it'll enable us to look at how that staff list has changed over time and obviously give you some useful information So talking about useful I hope you found that video useful and it gave you the answer to maybe what you're searching for or it's just enlightening you on New functionality that we have available to us and something that you could maybe embed into your work to really improve the data That you're currently capturing if you did enjoy this video, please don't forget to give the video a like It's not only greatly appreciated by me, but it helps that all important youtube algorithm And if you're new to the channel or this is your first time watching one of our videos or maybe you've watched our videos before Please don't forget to subscribe to the channel and hit that bell notification button That way you'll be notified of all of our videos as they come out in the future Lastly, thank you once again for watching and we'll see you in the next video