Filter SharePoint Data in Power Automate using OData Filter #PowerAutomate #ODataFilter #SharePoint

hey guys welcome back in today's video we're going to talk about power automate or data query and what is power automate or data query so orator query is what you can use in your power automatic flow to filter the data that you are requesting from any data source like sharepoint so let's take an example let's say you are trying to query data from a sharepoint list or library and you don't want all the data from the sharepoint list instead you just want some of the data based on your criteria so what you will do whether you will query entire data and then apply your condition no that's not the best practice so instead you can use these odata query filters when you are creating the data and get only the data that you need so this is what we're going to learn in this video we're going to look on different type of columns that sharepoint had and what is different type of auditor query filters that you can apply on these different type of column so let's start without wasting any more time [Music] [Music] hey everyone i am deepak srivastav welcome back to my channel and if you are first time on this channel don't forget to subscribe like and also hit notification so for today's video i have created this sharepoint list that i am going to use as data source for my power automatic flow and in this particular travel request list i have all different type of columns and i need to quickly show you because that is the important point we are trying to learn and understand how these different type of columns you can use in power automatic flow and apply the orator filter so i have single line of text date and time person group choice column number column currency yes no all of different type of column that you can possibly use in your sharepoint okay the first type of column that we're going to use today is single line of text now you know single line of text column is our title column or any column that you generally use to put any text because we are talking about or data filter so that means we're going to talk about the different type of operations that you can use now filter can be i need to see every data where something equals to something or something is not equal to something or something starts with or contents right or greater than less than these are the different type of filters that you can use in odata so what are the different type of operator that you can use in single line of text the first one is equals to so you can compare a value and then pass that over data to your query and then sharepoint is going to return you only the data that is fulfilling that particular condition so test this scenario we're going to jump to our power automatic flow we're going to create a flow and start applying these different type of auditor filter and see how it works so i logged into my power automate flow as you can see and i'm going to create a simple flow uh the trigger can be different in your scenario it can be automated instant scheduled okay so this is the manually trigger flow and as i said your trigger can be different the next step that i am going to do because we are trying to query the data and our data source is sharepoint i'm going to search for sharepoint and i'm going to search the action called get items because this is the action that's gonna give me or that i can use to query data from a sharepoint list or library there are different parameter that you know you need to select you need to select the site address from which site you wanna query the data and then also the list name my list is travel request okay now if you just look at this one there is nowhere you can see the filter property right where you can apply the filter so to see that you need to click on this show advanced option and once you click that this is what you will see here the filter query now as i said we're going to start by column by column so first we're going to look how we can use this filter query to filter the data for a single line of text column in my list i have this strip title it's a single line of text okay so let's say i'm gonna i need to apply a filter here to filter only the data that i need based on my filter criteria so to apply the filter query is pretty straightforward first thing you need to know the internal name of your column which you are going to use for this filter query okay so you need to make sure that you know the internal name of your column okay my trip title column is actually i rename the title column okay so this is my title column and how i can check i can go to the list settings and i can see here or i can click into this particular column in the url i can see the name of my column as you can see here right this is the title so you just need to make sure that you know the internal name because that's what you need to use in here so now i would say title okay this is the syntax so this is how you write the audio filter query and then if you remember our first is equal so this is the eq that's the equal so you need to set it equals for any of the text type column you need to use the single quote so single quotes in between the single code i can write my text that i'm trying to filter on so title equal to the title name and this is all you need to column name internal column name operator equals to and the text i'm going to save it and run it okay so flow completed successfully and if i go to my get item this is also completed successfully now just to see the result i'm going to add another action so we can easily read what we are getting i'm going to add a compose action and then just the value okay and this is the result of our get item i can rerun it okay and as you can see here right one result came back for the title that i have here so that was the first very simple equals to and you can apply this to any type of single line of text column the next we're gonna see is not equal to okay i'm going to edit this flow the logic is very simple instead of eq you just need to replace this particular operator so i will use an e because n e is for not equals to and i'm going to run the same flow again this time it's going to give me all the results except that one that has this title okay and as you can see here this time i am getting more and more results because i have multiple items matching the criteria that i provide next we will move to starts with so it starts with is nothing but you are trying to say that you are not going to give the entire text of the matching column that you are trying to filter on instead you are just saying that ok if anything starts with that particular text give me that information okay so to use the starts with now you need to change the syntax a little bit so you need to type the starts with because that's the name of the operation okay then you need to pass the name of the column which column you wanna check this starts with and then i'm gonna just type my text here okay so you are saying it starts with the name of the column and what text it should be starting with same i'm gonna save and run it okay so flux and completed successfully it's gonna get some results and if you see here there is only one item that i got because if you look at my list the credit criteria that i provided only matching one if i change this to just trip then i will get more than one so this is how you can filter or you apply the filter checking this starts with but it starts with only gonna check at the print like at the beginning of your of your text column let's say you wanted to find anywhere in that column so that's where you use the next operator that is sub string off this is actually similar to contents okay so for service string off you need to start typing service string off so substring off and this time what you need to pass you need to pass the text first and then the column name so i'm gonna say this is my text that i'm trying to find into my column that is title okay so if i can find this abu kay in anywhere in my title give me those state those results okay this is how you can use this substring off or contents okay so flow completed and then this time it filter for and i can now you can use the substring off to find my given text anywhere in my text column that i am providing so these are the four operations that that you can use on single line of text column now you can use the combination of these different type and how you can do that let me quickly show you that so let's say you are applying this one and you also wanted to apply another operations okay so if you wanted to have multiple operations you need to separate them by bracket so i'm gonna close and close this entire operation into one bracket okay and then what you can do here if you want or operations or end operation you can use either so let's say i'm using r so i'm going to say or okay so i'm saying whether this condition where substring of or okay so now i have two condition here well first one where i'm searching for a given text into a title and second i am saying title equals to and i have the r so any of these condition is going to true i should get all the results now if you want to create more complex you're going to keep combining or closing these into the different brackets so for example i want to make this as one condition and apply and what i will do i will close this into the bracket and then have my end operator here right so you can keep doing it and make it more complex as you want okay here i'm going to keep it like this test okay so completed successfully and our both condition should be true and hopefully i should get two results this time so yeah so we get this one where this text exists and we get another result where title equals to that right and if i change that all to end i will not get any result because those conditions are not really matching so this is the way you can have more complex conditions and this is going to apply to any further operations that we're going to see in this video if you want to have more complex you just need to make sure that they are closing into the bracket and then you are using or end operator at the right place as you need the next type of column we are going to see is the choice okay the choice column is very similar to the single line of text column when it comes to the or data filter query you can have all those similar operator like equals to not equals to like we have in our single line of text it starts with and so string off so these are the same operator that you have seen with single line of text can be used with the choice column as well and we're going to quickly see nothing is going to change the only thing that you need to change here the name of the column so now instead of title because that was my single line of text i'm going to use my choice column that i have here in my airline field okay so this is the internal name i know so this time i'm saying airline equals to let's say emirates okay so now as you can see here the odata query is pretty much standard you are passing your name of your column equals to and then what value you want to filter on i'm going to test it quickly and now i have all the data where airline equal to emirates the not equal to start with substring of all going to remain the same as we have seen in our single line of text no change the only thing you need to make sure they're using the right column now the next we're gonna see the type number okay so the number type of column comes with more than what we have seen in the single line of text and the choice column we can have the equals to as we have seen in single line of text we also have not equal to any as we have seen in single line of text and the choice column also but now we're going to add more different type of operations here so you can also have less than then you can also have less than equals to so less than is gonna filter all the information all the data where you will say okay show me everything here number column less than 100 less than equals to is going to help you show me everything where number column is less than equal to 100. so it's gonna include 100 into that then we have the greater than and greater than equals to as well okay so these are the these are the type of operations or ordered operations you can apply on a type number column you're going to see one by one each of them and see how how we can use how easily you can use it in your power automatic flow okay so as you can see in my list estimated airfare i kept it as number column okay so i'm going to use this estimated error pair let me get the internal name so this is my internal name for this column go back to the flow okay estimated airfare equals 2. because this is the number column you don't need to use the single quotes you can just type the number that you are trying to compare with you may have noticed by now that this actual filter query writing the query is pretty straightforward you're typing your name equals to and then i type the number that i want to compare it with i'm going to run this one flow completed and i get that one record that has this 5 to 1 8.28 so now i'm going to change this and i'm going to say less than okay and what i would say the less than 4000 okay so now what i'm saying show me everything where estimated airfare is less than 4000 remember i am saying less than not less than equals to okay so we're going to run it and fill flow completed and i should get everything that is less than 4000 now let me quickly change this to less than equals to that is ali okay and this time i'm going to use this exact number three four nine nine five eight three four nine nine point five eight because i'm using less than equals to i should get this record and any record that is less than 3499.58 perfect right so this is how you can apply it now with the number column the other operator are also very similar greater than is going to give you anything that is greater than that number and greater than equal to is going to give you everything that is that particular number and greater okay the next we are going to see the currency column the currency column is also very similar to the number column and it gonna give you all those different parameter that you can use so you can use the equals to you can use not equal to you can also use the less than less than equals to greater than greater than equals to okay so these are the similar what you have with number but with currency column you will also get few more so now you can also have starts with and also the substring off so the currency column is providing you the maximum number of operator that you can use and if i go back to my list settings i have estimated hotel cost is my currency column okay so i'm gonna use my internal name equals and then i'm going to type a value and remember one thing with the currency column currency column you're going to treat as a text column so you have to apply the single quotes as i'm doing here okay test okay so completed successfully and i should get that one result where the cost is 100.38 okay similarly these other type you can use not equal to less than less than greater than okay so i'm gonna try this time greater than okay so what i'm saying that give me all the results of all the record where the estimated cost for the hotel is more than one thousand dollars completed successfully and now i should have all the record where the cost is more than one thousand dollar awesome right so this way you can use any of these different type of operator similarly if i go to the next set where we have two more starts within some string off let's see the house starts with work okay so if you remember in our first example of single line of text it starts with i need to start typing then give the column name and then you type that text or the value that you want it to be starting with so i'm going to say 100 right so anything 100 1000 10 000 should come because that's the starting point okay so this completed successfully and if you now look at the results say 1075 because it is start with ten one zero eight nine right so this is the this is how easy you can actually filter the data that you are having in sharepoint by applying these cool operator or operations using the odata query similarly you can have the substring of this is the contents so it can just give you anywhere in that cost or the currency column if you want to compare anything the next column is the date time so with the date time column you can have these different type of operation that you can apply you can compare a date that is equal to the not equal to so if you say if that column is not equal to this particular date then you can also have the less than greater than so if the date is before or after a particular date you can compare that okay we're going to take a quick example of a few of them okay so in here i have these two date column start date and end it i'm going to use the start date and my internal name for this column is travel start date okay come here i will start date equals to you need to use the single quotes that's good so if i'm going to say give me all the records where date is 10 now you can also do like this right so you can you can explicitly say that which month so now it is saying 11th of october 2022 okay and i'm going to test this okay and i got one record and in most of the scenario you may not be typing the date manually like i did you may be getting the date from some other system or from some other sharepoint list so in that that case you you can use expression that is the format date time and why you need to do that because you don't know the the date that you are receiving from the system is rightly formatted or not so use that that will help you and what you can do is very simple format date and time that's the name of the function and and use the date that you are getting so for example i'm going to use the utc now for for example utc now is a function that's going to give me the current date and time and then you need to apply your formatting so in our case you can apply so i'm going to use utc now that's going to give me the today's date and time and i'm going to format it in this way so month date and year and i'm going to change my filter query here and instead of using this i will use the output of my composite okay click test okay and i got that one record that is starting today and other than equals to of course you can use the less than greater than all of these different operator as we were using previously so instead of that i can say show me everything that is less than today right output that i've already formatted so i'm going to use that and this will bring me practically everything because my every start date is previous up today so it's going to bring entire set okay so this was the date and time the next we are going to use the lookup column so we generally create lookup column and we want to create a relationship between two lists right so we say okay this particular column is going to looking up to the information that is in my other list so for lookup column you have equals 2 so you can compare the value not equal to and these are only two options you have for the lookup so with lookup column you can only compare whether it is equal to or not equal to in my list i have created a lookup column called request reference internal name is different though and i'll show you that so the internal name is test lookup okay this is a good example i want to show you that what you are seeing on the display may not necessarily the right name that you should be using in your flow i'll go back to my flow internal name and as you know only two options i have equals to and not equal to so you see here saying request two and request one this is another list that i have where i have these different information before we go further let me show you the setup of this lookup column because that is very important for you to know so if i go to my lookup column right now if you see i am looking up into this list so that's it that is my parent list and then in the title column now you may create a lookup column where you are looking into some other column that that list has remember that because that is going to be very important because that the information you see here because i am looking up to titles i am seeing the title of that parent list listed here if you are looking up to any other column that will show up okay so i'm going to say test lookup equal to iq 2. will this going to work no it will not and the reason because this will look up columns you can't just compare the column directly instead you need to tell which column you are comparing that you are looking looking into that parent list okay so to make it work you need to change this query and actually slash and the name of the column that you are looking into okay so the name of your column in your list slash and the name of the parent column that you are looking into this lookup column now if i test it okay so completed successfully and i should have all the record where i have this request to as a lookup right so this is how you can use the filter or data filter for the column type lookup okay the next type that we are going to see is yes no checkbox column okay with yes no checkbox column you have following options you can still use equals and you can use not equal so what you can check with yes no whether it is yes or it is no it's very simple straightforward let's see a quick example in my list i have a column called approved this is type yes no checkbox if you look here the name of the column is also approved the logic is very straightforward simple as you know use the column internal name equals to here it's not yes no it's actually 0 and 1. so if you say 0 that means false or no and if you say 1 that means yes so i'm going to quickly test the scenario okay so now this has listed all the record where it is false or no or zero and if i change it to one then it's gonna show me everything that is true or yes okay now let's move to the next type of column that is person or group so the user field right and with user field you have equals and you have not equals so these are the two options you have with any of the person fields that you are using in your sharepoint list okay so let me go back to my sharepoint list and i have this requester column here that is the person type internal name is also requester now this is a little tricky so what you need to do here is you need to type the name of the column into the name then slash and then if you are comparing email then you need to type the email like the lookup column that you had similar to that right and then you can type email that you want okay so i'm gonna compare the this particular email here for that user single quote in the type email click test okay so flow completed and now i should get the record where the requester email is matching what i have passed there are two three records for this particular user okay yeah so this is all the different type of column that we have covered today different type of operators that you have in our data query for these columns when you are working with the sharepoint of the data source and you want to filter the data before you even start using any power automatic flow i hope this will help you and i would highly recommend that you should use the folder query thanks for watching keep learning keep watching thank you very much

Channel: Deepak Shrivastava

Views: 5,131

Keywords: power automate odata filter query, power automate odata multiple filters, power automate odata filter query null, power automate odata filter contains, power automate odata filter query sharepoint, sharepoint get items odata filter query, flow odata filter query, flow odata filter query multiple values, flow odata filter contains, flow odata filter lookup column, power automate, odata, microsoft flow, odata filter extressions, odata query, flow, power automate filter, flow filter

Id: wsDJqDIVgJI

Length: 26min 56sec (1616 seconds)

Published: Wed Jun 08 2022