 Hello everyone and welcome back to another video as the title suggests we're going to be extracting data from a SharePoint list and saving it to a CSV file but we're going to be adding another element to that which is applying a filter to the data we are extracting from our SharePoint list. So the listing question is what you can see on the screen now and you can see it's a load of people data. It's worth noting here that this is all randomly generated data so should any of this coincide with actual information it is complete chance so it's all just randomly generated data. You'll see that I've got multiple columns but one in particular is this city field and we've got about I think there's five or six different cities and what I want to do is I want to create a CSV file but only for the cities of Bristol and you can see one of them here. So what we'll be doing is using Power Automate to first obviously get all this information from our SharePoint list apply the filter that we want to do for the city and then of course save it as a CSV file and just to take one step further we'll not for one step further but you can see we've got an empty document library here in which we'll be placing our CSV file. So what we'll do is just jump straight into Power Automate and get on with it. So there's a couple of obviously navigation steps to get into Power Automate if you're not familiar with the tool so just look in the description of this video and I'll show you those exact steps rather than covering it in the video. So the first thing of course we need to do is a new flow and for me I'm going to just do an instant cloud flow. You may want to be doing a scheduled cloud flow if you want this to run on you know at a certain time or a certain day and of course we've got other videos in our playlist which should be linked on the screen somewhere now if you want to learn more about those those obviously those triggers. So let's just go for instant cloud flow for now and of course the all important thing we need to do is give it a title so let's call this people data, if I could call it data and I'm going to call it Bristol because it's specific to Bristol and again not important but we just know it's going to be a manually triggered flow for the purpose of this example. So the very first thing that we need to do is of course get our information from SharePoint. So in order to do that we'll obviously click on to new step and what I'm going to do is just type in here SharePoint which will give us all of our actions available or applicable to SharePoint. So for us what we need to do is get items click on that and then of course we just need to fill in these boxes. So site address so for me it's going to be my SharePoint site and the list at name we can see connected to that site it's giving me a list of all the possible lists available on this site for me it's called people data and then that's the key information it needs. So at this point it would extract everything from that list whereas obviously we know there's a filter we like to apply to it so what we're going to do is go into show advanced options and the particular field we're interested in is this filter query and you can see there's other options available to us but again for the purpose of this video we're going to be looking just at filter query. So if I go back and look at my people data so as in the list we can see that the field we want to extract is called city and we know that obviously we want to search for or filter on the value of Bristol so let's go back to power automate so if you're not familiar with OData it is really easy or simple to get started with again if you google any scenario that you're trying to work out you will find the results for you but for this scenario where we're literally just trying to filter based on an equals value all you need to do is provide our field name the two characters of EQ which obviously is short for equal and the search term or the filter term we want to use is Bristol so within single quotes so make sure they're single rather than double we're just going to type the word of Bristol and in another single quote so at that stage you may feel it's all sorted your city you know field here obviously matches matches the the field name city here but actually it's not or unfortunately it's not as simple as that what power automate wants you to do is be more specific with your field name so rather than just the description that you actually see in class as the field name we need to know obviously what the actuals we call it back end field name is for that particular field in order to do so we'll go back into our share point list and as you navigate to the top of the screen you'll see there's a setting settings icon so this cog that you see here so I'm going to click on that and then we can go into list settings and obviously you'll load up and then once we're on this screen we can obviously navigate down to our list of available columns and obviously go to the one of interest so for us it is city so we just click on to that and okay all this seems all good and this as you did if you didn't know already this is where you can obviously update or change the settings of your field but what we're really interested in is if you look up at the url and cast all the way over to the far right hand side you can see that it's given a reference of field equals field three so field three is actually the we call it the true name of my city column so what I'm going to do is simply copy that value there go back into power automate and rather than have the word city here I'm simply going to replace it with field three so again when you get started with like filtering stuff like share point this is one of those simple things that can obviously trip you up and cause a problem but this is what you need to do to make sure you won't have those issues once we've done that we've successfully done the part of extracting our filtered data from our share point list the next thing in our scenario is we want to create a csv file so all I'm going to do is go a new step here and I'm going to type in here quick simply create csv and luckily well not luckily but you can see it's the top one here create csv table so all you do is select into here and you will look and you can see we've got some dynamic content so for me I want to just select value so it's going to be obviously the list of items that we achieved in this get items here and we know it's associated with that one because you can see this step here it's called get items and you can see all the values captured within that step are obviously grouped under this section here of get items so we'll select a value in there and then once we click out of it oh no if you go to sorry if we go into show advanced options you can see we've now got this option here for columns so by default it will do an automatic or it will try to do an automatic match of your columns to replicate what is captured in SharePoint in your csv file what I prefer to do is just go into custom and this just allows you to make sure that you've got the correct headers for the value or desired value from SharePoint so what I'll do is well actually just to go step three is now so we've got employee id was the first field I would want and if I then click in the value section here and scroll down you can see that I've got my SharePoint field here employee id and then just carry on this list rather than just go through that slowly I just fast forward to the end having mapped all of those fields but as hopefully you saw in the video it's very simple to just make sure that in the left side you've got the column header you wish to appear in your csv file and obviously just map your corresponding value from your SharePoint list so once we've got that you know we've got this csv table ready to now save as a file so the next and final step I'm going to do is go into new step and here I'm simply going to do create a file and obviously there's a number of options available to this but the one we're going to want or as per this example is create file with SharePoint so once we click that again we just need to make sure we confirm our SharePoint site and our folder path so for me within shared documents I've created this example folder so once I've navigated to that I will just select example so in terms of the file name I want to just call this or I could just call this bristol.csv but the only thing with that is as this flow runs multiple times obviously it's just going to continuously overwrite that file whereas what I'd like to do is just capture a today's date just so we know obviously when this data was extracted so what I'm going to do is simply just after the web bristol do an underscore and then leave in the cursor where it is now I'm going to go into expression and I'm going to type into here format date time open brackets utc now and then do a comma and then in here I'm just going to enter the year before month and the day and also make sure that is in within single quotes rather than the double quote you've got there so in single quotes I've put year or yy yy capital mm and in lowercase dd so what this stands for is obviously the year month and day make sure you use capital m's with month because if you use lowercase m's it's actually going to get the minutes from the hour rather than the months from the date and then what this will do when I select on okay is you can see it's just insert a discustom piece of formatting in between the underscore and the dot csv what that will do is where every time this runs it will obviously save our csv file as bristol underscore and today's date so if you run this over multiple days you're going to have a record of when that file was generated you don't have to do this you could just put bristol dot csv but there's an added little piece of information there for you if you find it useful and lastly for the file content all we're going to do is click within this field and you can see create csv table step has created this output all we do is select that and lastly select save we can see that our flow is ready to go it's not giving us any errors so all we need to now do is go into test we're going to do manual test test a couple of buttons to press here continue and lastly run our flow and then what we should hopefully see shortly is it will update yet we can see that our flow has ran successfully and obviously we can see from the duration of time it took for all of our steps everything looks like it's done it in a really good fashion nothing's taken too long to produce so if we go into our analytics folder we can now see we've got this new folder it's been created called bristol underscore today's date and if we click into there we should hopefully just see all of our data for bristol so yeah there we go we've got obviously a reduced number of rows and we can see that for all of those rows the city that obviously each person likes to is bristol so i hope you enjoyed that video if you did please don't forget to give the video a like it's not only greatly appreciated by me but does help out all important youtube algorithm enabling other people to also find this video and if this is your first time watching our channel or you've watched our videos before and you've still yet to subscribe can i please also ask you to hit that subscribe button and also that bell notification button because that way you'll be notified of all of our future videos as they come out so lastly thank you very much for watching and we'll see you in the next video