Power Automate Part 9 - Sorting and Filtering using OData

welcome to this why is our tutorial on sorting and filtering using odata in power automate here's what you'll learn during the tutorial so we'll Begin by looking at what odata actually is and why it matters to you why should you care and then we'll look at setting up some example data for this tutorial for Excel and SharePoint what I'll then do is show how to use odata to sort and filter Excel data and then we'll do the same thing with SharePoint the reason for doing them differently is each one has its own issues and those issues are different for Excel and for SharePoint what I'll then do is show some Advanced filtering techniques we'll look at Dynamic filtering so you can use a variable to hold parameters and finally we'll look at experimental features which is a way to make it easier to write to odata filters and in this point you'll probably curse me and wish I'd shown you this at the beginning of the tutorial rather than at the end but that's enough of looking at me I think it's time for me to vanish as I tend to at this point in the tutorial and let's get started so let's start with what odata is and why it's important so I've gone to odata.org it's the first thing which comes up when you search for a data and I presume the most important site and what this tells me is that odata means open data protocol and it's basically a convention whereby you can get information from a website and apply filters and sorting I'm sure it does a great deal more than that but for our purposes that's what we're interested in as to why it matters have a look at this flow I've called it example of inefficient flow and that's because it is it's based on a SharePoint list of 1200 films we'll come back to that in a bit and it doesn't apply any filters so if you look at the odata information the filter query here is empty and what that means is it will get back all 1200 films I didn't like this because I know it's going to go around very slowly on the cloud Microsoft seemed to sulk when you apply when you send large requests through and the responses get slower and slower but Microsoft don't like it either you can see there's a flow Checker here at the top with a red symbol next to it and if I click on that it's got a warning telling me that if I use get items without a no data filter I'll have slur performance and if I ignore this and try running it the first time it gives me a warning too so that's what oh data is let's now have a look at the data sets we'll use for our examples for SharePoint and Excel so here the examples we're going to use for this tutorial there's a file attached to this video called films with genres and you'll be able to get that by looking at the program notes for the YouTube video and if they'll tell you which why is our web page you need to go to to download this file so that's in Excel and what you can do is bring that into both Excel online and also SharePoint a very quick reminder of how to do that for a SharePoint what you could do is choose to create a new list you could then choose to get the list from Excel choose to upload a file choose this file called films with genres it contains two tables make sure you choose the films table and you can click on next and proceed and it would create the list but I've already done that and it's called films on the left hand side if I click on that you'll see the contents of the list Aster Excel you can create a new folder possibly I created one called o data and within that you could choose to add a new file sorry uh I'll upload a file choose file upload choose to get the file file fills with genres and you can then upload that and if I now click on that you'll see that the contents of the work work complete with two worksheets films and genres that's what we're going to use so that's the data we're going to use for this tutorial what we'll now do is focus on Excel and then on SharePoint so to illustrate how you can sort and filter data using odata with Excel let's create a new flow it's an instant Cloud flow what I'll do is call it odata and Excel manually trigger it and we'll add a step get the information from Excel and I'll choose list rows present in a table and as always go through the process of choosing where I'm getting my information from OneDrive for business and then OneDrive choose my file which is in the odata folder and then choose my table which will be at the films table and then what I'll do is add a new Step beneath it to display the results of this using an HTML table and I will choose to display from the values from the list of data returned from my previous step choose custom columns and what I'm going to do is display the title The genre and the nominations you can actually just click in the header and then go into the next column to create rows but if you do this they don't actually have any headings so it's probably a good idea to go back in and put the headings in so I'll call my film name nominations that's how many Oscars of film is nominated for and the genre and we're good to go so that's a fairly straightforward flow what I'm going to do now is show some things you can do without data and to be honest what I'm really doing is highlighting of the various problems you'll have let's start with ordering and let's do some do something which I think probably will work we're going to sort them by a genre so that's that column there I can choose genre and I can type ascending ASC or descending I'm going to go for descending DSC and at this point if you program an SQL you'll have the illusory feeling that this is going to be just like SQL it isn't so now what I could do is save this and run it and when I run this what I'm expecting to see is all the films but I think it's westerns is the first genre in Reverse alphabetical order come first and then I'll work backwards through the alphabet let's see if that actually works it's done and if I have a look at the output from that in my HTML table and you should see I've got excuse me three columns but strangely I'm not seeing the results and I've had this before and I just wonder whether it's because I've got this file open it's suggested last time that wasn't the problem but let's actually try it again so what I'll do is try running this again I haven't made any changes whatsoever apart from closing down that file and this time does it runs successfully it has so maybe that was a problem so you can see that I've got my westerns first before by my Warframe my Thriller it's all worked what I'd like to do now is to change this so I sorted by genre I'll change that to ascending and then within genre I'll sort it by the title this is valid uh oh data there's nothing wrong with it but I don't actually think it's going to work let's try it and see if my pessimism is Justified but you're running this uh and if I run my flow and choose done I was right my flow run has failed and the reason it's failed is because almost unbelievably only a single expression is supported in the order by clause for Excel when we get onto SharePoint we'll see that's not true Excel seems to be far more restrictive about what you can actually do with it as a data source so you simply can't do what I just tried to do so let's try something else I'll just go at editing that and what I want to do is to filter it I was rather sorted by the runtime now the runtime has got a spacing so let's see if we can do that I'll delete my original order by I have not found any way to do this whether in filtering or sorting single quotation marks like that didn't work square brackets like that don't work replacing the space with an underscore doesn't work I can't solve this problem it appears to be the case that the only way to solve it is to go into the Excel spreadsheet and change the heading to not have any spaces in they simply can't solve it so if anybody else can let me know the answer so that's sorting let's have a look at some filtering so what I'm going to do is filter it to show all the films which got at least 10 Oscar nominations so I could type in nominations is um let's say 10 more than 10. I can type nominations is greater than 10. and let's see if that works it's the first thing you would instinctively try particularly if you come from any sort of computing background which I imagine most of you do does it work it looks it's looking good but not now and the problem is the greater than character isn't valid and the reason I show you this is otherwise you probably won't believe me what you have to do instead is Type in GT for greater than or GE for greater than or equal to LT of less than or Le for less than or equal to EQ equals an n e for not equals two later on in this I may bring up a slide showing that in this tutorial so that should now work did let's try it let's save that and then if I test it by clicking my test button choose to run it and it's failed again this time it's failed because in Excel only single equal and not equal to operations are supported you can't even do comparisons again I can barely believe it when I saw this so the answer to how you do what I was trying to do is you don't just to prove you can actually get filters working let's change that denominations it's equal to 10 a far less useful query admittedly and if I run this you will see it lists out all the films which got exactly 10 Oscar nominations but whether that's a useful thing to be able to do I'm not so sure and you can see them listed at the bottom there so these are some of the limitations of working with Excel what I'll do now is look at some ways limitations of working with SharePoint and then we'll look at some more advanced filtering you can do in both environments so what I'm going to do now is pretty much the same thing for SharePoint and what we'll discover is there's probably just as many issues but they're different issues so there's my SharePoint list and what I'll do is create a new flow an instant Cloud flow I'll call it odata and SharePoint and what it'll do is get some data from SharePoint I'll use the get items action choose my SharePoint site waiting the customer a second or two for that to appear choose my list which is going to be films and then I'm going to add a new Step beneath it now there's two reasons for what I'm doing to create an HTML table one is I want to be able to output something so that's the conventional reason for the second reason is if I choose to show custom columns then what I can do is get information about what these columns are called so I'll start with the title and then have the genre and then have the nominations titles there that's good that's what I'd expect it to be showing you it's there that's good that's what I'd expect it to be nominations presumably it's just underneath yeah it's not it's the order this is just absolutely bizarre occasion is alphabetical and then it veers off into some other word order so by the time you get down here you're thinking clearly I've misunderstood things it's not going to be there but if you have the confidence to keep going and you really do need a loss of confidence you'll eventually get some more fields and there's the nominations which I've just actually put in the wrong place I'll put it in the right place so that's weird quite why it shuffles the fields like that I couldn't tell you so those that's my basic flow what I now want to do is sort it by genre just like we did with Excel I'll go into my properties here and I will choose order by plug-in genre and ascending and again this is something I just want to show you because um just save that because again you might not believe that this isn't going to work without actually seeing it for those of you with a good memory will remember why it's not going to work you can see it's coming up with a warning there that's because although I'm sorting by the data I'm not actually filtering it so Microsoft unhappy that I'm going to be downloading lots and lots of rows I can live with that if you can so my flows run but it's crashed and the reason it's crashed is it going to tell me this I think it will if I wait patiently long enough I think it will come up with a message on the right hand side that it can't find the field called genre and the reason you can't find it is because the field isn't called genre no it just says fail to fetch it looks like it's called genre but you remember that the only true way to find out what it is just going to editing that is to let the mouse linger over it I'll automate anyway and it's actually called field underscore six so although I quite like the sound of genre ascending I'm going to choose field 6 ascending instead and I'm going to sub sort it by the title which is actually called title so that's a special name for a SharePoint list field and where's this didn't work in Excel because you can only sort it by one key at a time I'm pretty confident in SharePoint it is going to work that limitation doesn't exist just ignore the nagging from my flow Checker and try actually running that and if I have a look at the output from this you can see my flows are sorted so I've got all the action ones first and now the first genre in alphabetical order and their subsorted alphabetically by the title so that's all worked perfectly so some things work better some things work worse so that's sorting I think it's time to do some filtering your SharePoint just to show how that works and what we'll do is filter it to show all the ones where the nominations is greater to 10. it's what I wanted to do for excel so my nominations field is called field underscore four as you can see all I need to do is type field under score four let's say greater than equal to 10. and although this didn't work for excel I think it probably is going to work for SharePoint so it looks like they put more work into getting a data working for a SharePoint data source and perhaps for an Excel One and if you look at the output you can see all these films got more than 10 nominations 10 or more nominations so that's the basics of sorting and filtering using Excel and SharePoint let's have a look now some of the more advanced things you can do so what we'll do for some Advanced filtering is look at four things we'll show how to test for null values how to test for dates not the combine criteria together and how to use some odata functions and we'll do it using SharePoint because many of these things won't actually work for excel so what I'm going to do is start by showing how to test for null values because I don't want to have to reinvent the same Wheels I'm going to save my odata and SharePoint below I'm going to rename it as other things you can do with filters and then after it saves it what I'll do is go in and make sure it's able to run then click on the three dots and choose turn on and then I'll go into editing it and that saves us all a little bit of time so I'm going to get items and choose show Advanced options and I'm going to get rid of any filtering when you order by that I've done before so what I want to do is show all the ones where the genre is null is null so I need to remind myself of what the genre is called it's called field underscore six what I could do with this is Type in field underscore six equals EQ and then just the word null and if I run that you should see that it gives me I hope an empty table and the reason for that is there aren't any films with a null genre just have a quick look at that you can see all the genres are filled in so I'm expecting it to work but I'm not expecting it to give in the output and sure enough it doesn't although I had to wait 10 seconds which you didn't see for that to happen so what I'm going to do is go and find Titanic with my colleague Andrew hates as anyone who's been in his courses will testify I'm going to edit it the reason I'm not choosing to edit it in Grid view is whenever I do that it hangs maybe I'm doing something wrong so the genre for that I'm just going to delete and save my changes and I now have a genreless film what I can now do is to go back out of this and I can run my flow again and what I should see this time when I run it is that I get a single film Titanic any flow listing Titanic can't be all bad surely that's how you can deal with nulls so the next thing we're going to do I'm going to stay with the same flow is show how to pick up all the films for example which were released on after the first of January and 2010 not that many of them actually but the problem I've got is the release date isn't actually stored as a date it's stored as a number and although I'm sure it's possible somehow to create a column which will translate that into a date and can find an easy way to do it so what I'm going to do is cheat slightly and I'm going to load up a new list copy the list but more carefully this time I'll create a new list I'll base it on my Excel file and I'll upload my list of films so far so the same but with a release date instead of storing it as a number I'm going to start to the date time and then choose next and I'll call this films with dates and she's great so while it's busy refreshing that I'm going to go back in to my flow and change the source of this I think I may need to just refresh this and see if I can get it to do that so I can go back in occasionally in this series of tutorials I'm going to give the impression that my power automate is working much quicker than yours I had to wait two or three minutes for this to come up so what I can now do is set a filter and the filter is going to be that the release date is greater than equal to the first of January 2010. but that begs the question what the release date is called so what I'm going to do is add it into my list and have a look it's called field underscore two I guess I will actually give that a name so in my filter I can say we're field underscore 2 is greater than equal to and the safest and most reliable way to refer to any date is in international format so you start with a year and then put a forward slash if you want and then put a month so I went to January which is a one and then another forward slash and then A1 and that's a safe way of referring to a date so let's see if that works if I now try running that what you should see is it gives you all the films released on or after the 1st of January 2010 and while there's a lot of them now I'm quite so many in our database now look at the output of that you'll see all these films are quite recent ly recent anyway so that's worked that's how you can deal with dates the third thing I was going to do is combine that so what we'll do is show all the films released on after the 1st of January 2010 and which are action films so just to do that I just put and was it field underscore six it was up until then until the underscore 6 equals and then I need to put action in single inverter commas and if I try running this I should see less films quite a few less films we checked if they're only I'm taking a bit of a punt on this one it's going to be disappointing if there aren't if I run that and scroll down you can see the output of this is an HTML table containing only action films released oh that's quite a lot of them since our date and you could also use the word or instead of and but clearly in this case that will give me virtually every single film because nearly every film was either released after the first of January 2010 or equal to action there's not many exceptions to that so the final thing I was going to show is a no data function or operator there's the main ones you can use I promised you I'll show you all the operators equals not equal to greater than greater than or equal to less than less than or equal to and there they are but you can also choose starts with contains and substring off although that seems to be specific to SharePoint so what I'm going to do is show all the films which start with the word star I'm hoping to pick up things like Star Wars so I'll get rid of my existing filter and type in starts with there's not much giving me any help with this so the thing I want to refer to is a field called title but it's not called it is called title let me just put in title and I want to find everything which begins with word star it's not case sensitive so I can just put that in any combination of case I like and if I try running that you should see that it will give me all the films beginning with a star again I don't actually know how many there are these I haven't checked this but there has to be some surely and there they are starskin how it should have thought it so that's a quick guide to some of the more advanced things you can do with filters no data what I'd like to do now is show something called Dynamic filters and the example we're going to do is to show all the films which were released in the last let's say uh 5000 days what I'd like to do is to be able to create a variable to hold what the date was 5 000 days ago and then reference that in my filter so that's what we're going to do so I'm going to add a variable so I'm going to click on add an action and I'll choose initialize variable and I'll set my variable called let's call it reference date and it's going to be a string of text you might have expected that to be a date but it's actually going to hold the date as a string format yeah yeah yeah month month day day as to the value it's going to hold this is going to be the first of a complicated Expressions we'll create there's a function in a Powers Mode called UTC now which will give today's date what I want to do is not give today's date but give what it was 5 000 days ago so I can choose the add days function and add minus 5 000 days to that and that will give me what the date was 5 000 days ago as a date but I want to restore it as a string of text so what I'm going to do now is to apply the format date time function without that type which will take that date and format it nicely in this case in the format YY Dash mm-dd and you'll notice that you need to use capital letters It's quite common in Microsoft software to refer to the month now if you're wondering where I got all these functions from functions from I could have looked in all the different categories to find them so there for example is UTC now and got more help in them and we may do that in a subsequent tutorial for the moment I just want to get this working so I'm hoping that when I run this that will contain the date as it was five thousand days ago so what I can do is go to my get items uh task and I can filter it to share it where the reference date which is no I'm going to have to go and look at it here the release date rather field underscore two I should have remembered that where field underscore 2 is greater than or equal to the value of my um variable what I can now do is click on variable data sorry reference date and it will substitute that in if I now run this quite clever little query a little flow I guess I always say so myself and then what I should see is a subset of all the films but in fact I see an error message and the reason is can you see if I can zoom in on this or show it I've missed out the inverted commas so what I need to do is edit this and go back into my filter expression I need to put this needs to be done carefully the single inverter comma there and a single inverter comma there to complete my syntax because it's a string of text I need to include it in quotation marks and although it's being returned as a string of text it doesn't come in with the quotation marks and this time when I run it it's working and I can see all the films released in the last five thousand days and that looks fairly plausible if I look at my initialized variable and look at the results of this you can actually see that 5000 days ago was the 15th of December 2009 but the beauty of this is that will change over time as the value of my variable changes so for the final part of this tutorial something which I feel I should probably have told you right at the very beginning which is how to use experimental features to help write your own data I think when you see this you'll probably curse me so I can click on the settings button and choose to view all my power automate settings and I can do something we did in an earlier tutorial as well and turn on my experimental features now at this point it will reload the web page so you will lose any changes you've made to this flow but I'd already saved everything in it if I then go into my flow sorry into my get items task and have a look at the advanced options how beautiful is this not only can I see what the operators are I don't have to remember they're called GE or gt or whatever it may be but most importantly when I click on the drop Arrow I can see what the fields are called so I don't have to remember the field numbers so I think while I wasn't that big a fan of the experimental features where for the expression editor oh data editing it's a must but I must be honest I don't do a great deal of very data editing when I'm writing my power automate flows and I don't like using experimental features because they may disappear at any time indeed that's exactly what this message says so I'm going to turn that back off again

Channel: WiseOwlTutorials

Views: 1,281

Keywords: wise owl, online training, free training, tutorial, online tutorial, free tutorial

Id: so3oXOo7lsM

Length: 27min 32sec (1652 seconds)

Published: Wed Jan 03 2024