Power Automate - How to filter Excel date columns using ISO and Serial Number formats?

hi in this video we will have a look at Excel date filters using power automate so here is my Excel sheet and I have got two columns name and due date So the plan is to filter by today's date which is 7th of January 2023 so if I filter using the date I should expect these two records Smith and day okay so let's start building the flow so before we go into the power automate build I would like to say you need to create a table otherwise without a table you won't be able to work within power automate okay so for all the Excel sheets you need to have a table so make sure you create a table for before you go and read or write the informations um using power automatic right so let's start building the flow so suppose you want to filter uh you know every day you want to check for today's date against the certain dates and send a notification email or something so for that I'm going to use a scheduled trigger here so that's a current trigger so that's a scheduled trigger here that's our current sugar and I'm going to say it's going to run every day okay the next step is the Excel connector okay so there are two XL1 Excel connectors here so I'm going to use Excel online business one here okay and then list rows percent linear table and here we need to select the location of the Excel sheet then the document library that is my mine it's a OneDrive where I stored my file then the file where it is stored under so the location it is under the flow demo and all members.xlx then at this time what uh what the connector will evaluate is it will look Autumn it will look a table access or not so if there is a table you should be able to see under that drop drop down box you see so without that you won't be able to you know do any operations in power automating for the Excel Excel sheet right okay so I did the members now yeah then compost so let's have a look at the table then so that's the due date see the due date so I'm going to put a due date here and try to run this floor now okay flow ran successfully and I can see see there is a number I got so that's the first uh there are three records there in my Excel sheet so all of them turned out to be some numeric number here so the reason for that is you can see the date format and the default format is the serial number that's the reason you got that okay so I'm going to switch this to an ISO 8601 format okay and also I would like to say here is the format of the due date so the format cells of the due date I haven't set anything specific to that okay you could set your date you know I said 8 feet but I haven't set anything here okay so that's that and now I'm going to run the flow again and see what happens foreign format but it returned me as YY mmdd along with our minute seconds millisecond sand or yeah and there are three records also there so how do we filter this so there are two ways we can filter it if I set it to ISO 601 I'm going to delete this apply to each now because I don't need that now and the next thing I need to do here is the filter array here filter fill to the right okay so under the filter array I can say value and then here what we need to do here is we need to have a format a specific format we need here so under the expression we need this here so I'll explain what the expression is for my Delta item question mark would you write that is you know this due date is the one of the property values coming property value coming from the previous step so that's item question mark do you write then comma YY mmdd so I'm ignoring all the hours minutes and seconds which I shown earlier okay so that's that is equal to comparing with today's date so what is the today's date one so today's date syntax is going to be similar but instead of the item I go I'm having UTC now okay and except everything is the same format date time UTC now YY mmdd here it is I'll put these two expressions under my video description Okay click place the cursor here and click okay that that's done and now I'm going to run this floor now and to see what software we are going to get okay flow right successfully and you can see here I got two records for today's date you can see that that's today's date and this is today's date about Smith and daily here okay so now edit again so how do we trade this so to iterate all I need is to um under the control go to the apply to each because it's in an array now so take the collection of that array which is the body then inside that if you want to trade those two values add a compost and under the combos we know the property value it's called uh you know the one called the name and another one is called the due date so see the name and the due date so you need to put exactly the same as here also so for the expression it should be give me the name which is item question mark square bracket name oh I forgot the single chords there we go and I'm putting a hyphen there and putting another expression at the same time so item because the mark do you date here we go let's click OK that and I'm going to run my flow again so hopefully this should give me uh you know a bit more readable format than earlier which I showed the results so uh now under the apply to each I got two records and I'm expecting two records only here which is today's date Smith and date and if I go here you can see Smith and daily here see again see the format to come back to this then so if you want to just show the you know the date in the uh in a proper format what you could do here is you could say for what date time bracket and that's item and I'm going to format with comma then the syntax is DD slash mm slash YY that's the 40 there are different format out there so this is my preferred format and then click on update that's done now and click on the test and run again and we will see the results hopefully in a more user-friendly date format here we go see that seven one twenty twenty three seven one twenty twenty three so it's all good news now here okay now let's go back I'm going to delete this now so we know how to iterate the values also after filtering the array okay so uh okay I forgot to say that so in this case then if you want to know to face someone you can have the sent email and under the you know go to the office three three six way Outlook connector send email and then here you can say the two address is coming from there then probably you can say add a dynamic content and then you can click on the expression and type item question mark if you got an email address you will say email address something like that you know and that email address should be part of your Excel sheet that's one of the property values but I don't have I don't have it here so suppose the instead of name it should be it is email address or something that's where I need to do it and click OK that so here we go and then you can send you know the notification email out so I'm going to delete this now and delete this so let's go in back to the the iso one so I'm going to put serial number now okay so what will happen when you use the serial number okay so uh in the serial number again uh you know we need to do a little bit work here to convert that date then okay so how to do that I'll show you now okay so before I do the filter right I'm going to delete this so earlier when we had the compost I'm going to add the combos here and select that and put the due date so we see that if that was coming uh you know converted that into a number you see the number yeah I'm going to add another compost also here and use another expression to convert it so I'll show you the expression so um let me put that expression here so that you can clearly see it let me close that put here as add note and put it here that here we go so see that so that's the formula you need to use it add days one eight nine nine dash one two dash 30 in of that item due date again the due date is my property name from the you know the column name from my Excel sheet then YY M1 DD so let's see what's of what's uh what what I'm going to get here foreign that's the format you know that's the right format so let's see uh from my Excel sheet here we go see that 8 1 20 23 so it's a 8 200 because I set the format as YY MMD so if I click on the next one that is today's date and I got another one also in today's date so here it is see that so how do we filter this using the serial number then so we can use the same technique to come you know you you use for the filter then so if I go back here and say filter all right again map the values and then here I'm going to use the same expression here which is this so that's going to give me uh you know that format of the converting that the serial number into the actual date format is equal to the same um uh you know the formula which we applied here earlier because that is for my date time bracket UTC now um two brackets commas two single chords YY Dash m mom Dash DD by Circle so there and click OK that is equal to if you want to keep it less than or greater than you know change it accordingly okay so that's what that is so now I'm going to delete this again I don't need that and now uh what I'm going to do here is I'm going to add a control here and to say apply to each and then map the values coming from the filter array of that body that's a result of that filter array then add a compose action step and here uh you know I'm going to say I know there are two values in that one is the name and the you know the date and here I'm going to say item question mark do you date click OK that and then I can add another compost as well if I want and I'm going to say get that name also so here under the expression I'm going to say item question mark name single quotes Okay that now trying to run this manually again and see what's the result is going to be so it should be same as you know that you saw one I also filtered what we did earlier well here we go it's been filtered so let's see the filter here so is it is it filtered only two records yes it's only 52 records that is for today's date and under the applied to each you can see uh that's again that numeric number so if you want to convert into the numeric number again apply the same formula which I shown earlier here in the meaningful format if you want and then the uh you know the value which is Smith and the next one is the daily one you can see here date so the let's change that also here the so it is this okay so I can remove this now here the and put the same expression here also oops place your cursor there and click OK that yeah now I'm going to run it again and you can see what the result is going to be okay so that's done so hopefully that date should be navigable format that's today's date it's filtered against today's date again that's filtered against today's date so uh you know in Excel sheet there are a couple of ways you could do it and you know my preferred preferred way is always is the iso1 because uh you know then I can rely on a proper date format I don't need to use this complicated formula also okay thank you for watching hope this is useful

Channel: abm abm

Views: 2,739

Keywords: Excel, Excel Date Filter, Flow, ISO 8601, Microsoft, PowerAutomate, Serial Number

Id: puR70yQMJKE

Length: 14min 6sec (846 seconds)

Published: Sat Jan 07 2023