 Hello everyone and welcome back to another video. So in today's video we'll be following on from our previous videos Which I'll do a link to on the screen somewhere now and in those videos We are using Power Automate to get a day automated daily extract of a SharePoint list and Store that into a CSV file on a separate SharePoint document library and that's what we can see here We're in the document library test archive and you can see we've got a list of all of these Extracts containing a date in the file name so we can see what date it was extracted and also to give it a unique name And if we were to go into one of these files, so we've got the latest one here the 1st of September You can also see we've got a column g which is our snapshot. So this is again It's just that exact date at which the data was extracted so this gives us the ability to obviously report upon this data daily and Ultimately we could see by day in a graph how the headcount has changed so to do that our total choice is going to be Power BI So let's jump into Power BI and obviously connect to this SharePoint library So the first thing we're going to do is go into transform data I'm just going to click the button as it stands here and If you're familiar with Power BI, you'll know that when you go into a new source You can select your desired and file type or connection type all I'm going to do is go into blank query Because we can just do this completely from fresh So what we want to do go equals and type in here SharePoint and The second one we have available to us is SharePoint dot file. So let's select that and Do open brackets so we can see the key variable or not variable But what it needs is a URL as a text So all we're going to do for that is we'll go back into our SharePoint site And we'll copy the first part of this URL so everything up until the site name So if you look you'll see sites slash analytics. So analytics is the name of our site So let's just make sure you copy all of that We'll go back into Power BI and within two double or double quotations We'll paste our string and select in this gray area below and you can see it's connected to SharePoint So at the moment what it's doing is for that SharePoint site It's just pulling through all the content available to us So they're very there are various numbers of ways you could obviously filter this data but for me, I'm just going to scroll down here and I can see that there's obviously a staff list here If I scroll to the right-hand side, I can validate that. Yes, these are the files from the directory that I want So it's test archive So in order to ensure I'm left with only these files, what I'm going to do is in folder path We'll just do a simple filter and we'll say text filters ends with and Once that opens up, we'll just type into here test archive and the forward slash Select okay, and you can see we're now left with obviously our desired role result a list of all of our files which contain the Date and the file name and we can see that they're all coming from that same folder if we just go into source and Scroll all the way down to the bottom where our desired files were You'll see that in fact within test archive. There is also this sub folder And if I select here, I think you can see it yet We can see what test archive daily email and then in another sub folder called other So the benefit of using ends with as I just did in our filter just ensures that we don't Also incorporate any of these sub level folders into our data as well But again, there are many ways you can obviously get around that but this is probably the simplest one for this example So let's just go back to our next step so we can see how our data is now looking and The next thing we want to do is to insert a new step so to do that I'm simply going to right-click and go insert step after so it goes the new step and At the moment what our data is doing is says it's equaling Hashtag filtered rows. So all that means is it's just doing exactly what is in the filters row step So if we go to here, you can see the data is exactly the same So all it's saying at the moment is the source for this data is just that equal the same as filtered rows But what we want to do is we want to combine all of that data So all we're going to do is build upon this we'll click after the equal sign and just do a space and Actually, we don't need double space. We'll go right in front of the hashtag and I'm going to do here is type the word binary dot combine and Then open brackets and then this is the source So all of the files are available to us in filtered rows At the end here we're going to do a square square bracket and write the word content because what we want to do is combine the Content from all of those files are available to us in that filters roads filtered rows step So once we've got that we hit enter and it's going to refresh and hopefully combine all that information for us And it's probably in adding adding some additional steps as well Yet we can see those building up on the right-hand side and there we go All that file information is now changed and we're actually seeing the content of all of those files So there's a couple of tidy up pieces that I want to do here So the first thing I do is go back to custom ID and We'll right-click and rename this and we'll call this combine Just again when we're looking at our steps from this view We know exactly what's happening at that step without having to go into it And we can see it's still refreshing up here by those yellow dots which are now gone Cool. So we've got combine. We've got to import our CSV Yep, that all looks good and then we go at the moment to change type Well, what I want to do is I don't want to have this step just yet. We want to do some slight tweaking So we'll just delete this for the time being So as you can see what it's done is the first row of our data set is actually containing our column headers And also as you'll see if we are to scroll down these column headers will be repeated for every file that we've combined So I think we had about 10 or 12 files that were combined into this data set So these headers are going to appear at 10 or 12 times So again all as always there are multiple ways to go about this But the first thing I want to do is I go into use first row as headers If you can't see this option Just make sure you've got the the home tab selected in the ribbon at the top here And then yet just select use first row as headers and what it will do Just basically nudge all the data up one And you can say once again, it's gone and changed all of our file change types All I'm going to do again is once again delete this out This is where I'm just being a bit fussy. But yeah, it just helps to tidy things as we go along So one of the first things we also need to do having or knowing that potentially Header or column headers are going to be repeated in our data set is we just want to remove those We don't want to see like a file name or whatever it is within our data when we report on it So let's just go into the filter for snapshot and all I'm going to do is we can see here We've got all of the dates at which data was extracted So I'm going to do is simply deselect snapshot from this field and all that's going to do is make sure that None of our data does not contain file names And the way to probably demonstrate that is if we just select everything and only select snapshot You can see we will end up with a list literally just of all of our column headers So in some way this is a good test just to make sure all of our column headers aligned throughout all of the files But for the purpose is this we want to just ensure that everything is selected apart from do not select snapshot So we do okay, and we can see it will update our filter Yes, so nice and simply just make sure that the snapshot column does not contain the word snapshot and that way we're reassure that we've got just the data that we want in here and Lastly, we'll just go and I'll do our formatting manually. So for us these first four columns are all text So let's just go and add these in just to be really specific to make sure again It does this every time the file is loaded. So go text text surname I'll take a little while on first name there. Yeah, let's go change that to text as well The same for email will format that as text. You don't have to do this I'm just rebuilding upon this just to be really extensive This is for us is date time and time zone. So in the same for end date date time time zone And then the very last one we've got here is our snapshot date So for us we want to change this for date So you can obviously try this yourself to see the problem But again the reason why I did this filtering we had here of filtering rows to remove the column names is Because we want to format this as date Obviously if we had text in here is going to throw back an error because it's going to say what actually some of your values aren't dates So that's why I've just done our filter and made sure we're left with the data that we actually want Before again, obviously we apply that the formatting to the data set So I think at this stage we're happy with everything as it is the last thing I just noticed as we've got the query name is query one doesn't look too good So we'll just change that to staff list which our data set is called and hit enter So you can see it's tidied up over here, and it's also reflected in the query name We'll now go close and apply and then what power be I will do Yeah, it's now back on the screen for us as you can see it's now evaluating Obviously making sure it's got that connection to share point and it's going to then import all of that data to us And obviously apply the steps that we've done in our in our query as well So depending obviously on the size of your data, this is going to take Longer or or or less time and again another one to think about here is in this example We're only working with a few like seven columns of data again if you've got maybe I don't know 2030 or maybe 40 columns of data in your file, but you don't actually need to use all those In that query that we worked on obviously that'd be a good time to delete everything that you don't need Just to make sure that you're not wasting time importing stuff that you don't need So lastly, let's just start creating a very basic visual. So let's just do a table. Maybe I did I think I mentioned the word chart earlier, but a table will obviously be easier All we're going to do is bring obviously go for our table visual and let's say we'll bring in the employee ID Let's bring in their first name and let's bring in the snapshot date So you can see obviously at the moment it's built a hierarchy for us But let's just do this drop down here get snapshot and select snapshot. So it's not a date hierarchy and What we could do here rather than having the Actually, I've done more than I need to do that. Let's just delete First name. We'll bring employee ID at the end here So it's snapshot employee ID and then all I'm going to do is select this drop down and rather than have don't summarize I'm going to change that to count and You can see what it's done for us is given us a count of our staff list per each date So at the more at the moment, sorry, it's obviously very boring because it's all the same data across the files Yeah, obviously it's doing that daily extract But because no activities happening on that list is just remaining the same But should yours be an actual working file where the numbers differentiate daily then of course you would in here see Obviously your numbers as they grow or as they move across this time period So I think we'll stop the video there and we'll obviously continue in future videos on how we can build upon this and maybe some other Reporting tips that we could use but I say for time being I hope you enjoyed that video If you have any questions about this content, please drop me a comment below and I will get back to you as soon as possible Also, if you did enjoy the video, please don't forget to give the video a like It's not only greatly appreciated by me but it helps the all-important YouTube algorithm and ensures that other people searching for this content are able to find this video and Lastly, if this is your first time maybe finding our video or you've watched videos before Please can ask you to subscribe to the channel and also hit that bell notification button That way you will be notified of our future videos as and when they come out So thank you very much again for watching and we'll see you in the next video