IT'S HERE: Work Queues in Power Automate - Full Tutorial

Microsoft just released work queues for power automated this is the biggest upgrade ever but what's a work you imagine a line in the supermarket everyone in the line is waiting to check out the people who arrived first all have special privileges like a fast track pass they are the ones who go to the front first this is similar to work use in power automate imagine a power automated workflow that reads 10 000 rows of data and then adds them to a system a common power automate scenario the best practice is to split the reading of the 10 000 rows and the system work into two workflows and that's where work queues come into play the two workflows are called dispatch and performer the dispatcher reach its record into separate Q items and then the performer process each queue item one by one this way we can handle multiple transactions more efficiently with better error handling and greater visibility on what's happening at each step my name is anas Jensen I'm in Microsoft most valuable professional let's learn everything about work use in power automate to find work queues navigate to the front page of power ultimate then you click this Monitor and there it is work use go click it let's create our first web queue I'll click up here saying new work queue and we can give our work queue a name let's call it numbers q1 we can give it an optional description and down here where it says item Default Time To Live we can Define when our items will expire let's just leave it for now we don't want them to expire at X minutes so I click create in a few seconds we have our queue that is right here let's add some items manually to the queue I can go up here say new work queue item and give it a name let's just give it a ID of 1001 the name is optional but it's nice to have some sort of a recognition of the individual item that makes it much more easier when we have a lot of Q items so our first Q item got the name 1001 and we can give it a value I'll say 2 comma 4 and that is actually two values so imagine that these are two values coming into our queue item we can add a processing node and an expiration okay let's not do that I'll just click create if I want to inspect all Q items I can see them down here or I can click see all that's much more better overview let's go add a few more items so we can work with them I go up here and say new work queue item let's call it a thousand two a new ID and the value could be 7 comma 4. click create let's add one more so new work queue item 1003 and we will give that eight commentary and click create so now we have three queue items here we manually added the queue items but in real life and later in this video we will of course add them automatically for example from an Excel sheet here I will go up to the URL we need this ID because we will use that to identify this queue so here I press Ctrl C on my keyboard just to copy it I now have it in my clipboard let's open up a new tab and create a flow there I can either go up here click new tab and navigate to another Power automate page or I can simply just press Ctrl shift k that will duplicate the tab here I'll go up to create yeah let's start by creating an instant Cloud flow and let's give it a name so here we will start processing our Q item so I'll call this numbers performer and saying manually trigger flow and click create that's it let's first add the ID of the work queue to a variable so I go up here to New step and I'll find an initialize variable yeah click it we will call this work queue ID it will be of the type string and the value that is the one we got in the clipboard I'll just do a control V here in the value let's also rename this it's much more easier to see what's going on in all flows so I click the three dots say rename here and then we can call it initialize variable work queue ID so now we stored this value in the variable let's take an item from the queue so I go down here to New step I move into Microsoft database and then I'll find a perform a bound action I can also search for that without moving into Microsoft database just be sure that you pick the perform a found action from Microsoft datawiss here we need a table name and that is work queues so I click this drop down here I can navigate all the way to the button and here it is pick work queues we want to pick an action and that is DQ that means that we take the first item from the queue we also need a row ID and that is actually this word qid from up here so I can click in here and use the variable just being created so right now what we do here is to initialize a variable we use that variable here to take an item from the queue and nothing else so what this means is that when we run this flow I can just save it it will just go into processing state of that item but we are not doing anything and we're not finalizing it of course we will do everything today so watch this video till the end let's go test it so I click test manually and test we'll run the flow that's it and our flow ran successfully if I go to the other tab here and right now we haven't refreshed so each of these items those are cute if I refresh the page you will see that the first item is now processing and again we are not doing anything well and we are not finalizing it so it will stay in the processing state I go back to my performance here let's go into the perform a bound action go down here to body control a control c will copy everything from this body here and one thing I want to show you let us scroll a little bit down is here in the body input you can see that 2.4 those are the input that we gave this Q item we did it manually let's work with this Q item so I click edit so right now we just take it from the queue but we also want to perform some sort of an action here I will find a parse Json because the output that we get from here we want to have it nice and handy click new Step here find pass Json and it might sound a little bit scary past Jason but what we do is simply just taking this output up here and make it more readable so we can use it in the dynamic content first of all we go into the content over here in the dynamic content you pick the body from perform a bound action then you say generate from sample press Ctrl V that was the body from before we click done now we can work with it let's also have some system work this will be very simple but imagine that this could be much more advanced it could even be desktop flows for now we will just do a pose yeah click it then I click in here and now I want to find the input from the past Json so I can either scroll down or search for it up here instead it's much more easy so that's the input and this input is the entire input that is we have 2.4 I believe it was a 2 comma form so what I can do is to again add some text to it this is just a symbolize the system work we will get more advanced later in the video so stay tuned here I can say the data and then we have the data got purchased so nothing too advanced this is just to symbolize that our automation workflow does something here I can click the three dots again say rename and I'll call this compose automation work I always like to rename a lot of these so I can see what's going on especially the variable actions and the composers we also want to update the status of this queue item let me move back to the queue as you see here this is an impermanent processing State I can even refresh to show it to you and we want to say each time an item is processed we want to set the status as processed so I go back here click new step and again you can move into Microsoft dataverse or you can simply just search for update a row here it is just make sure that you don't pick the Excel one but pick the data version that's why I often prefer to move into dataverse and find it there to reduce risk of errors and then in the table name we choose work queue items so scroll down here and find it here the row ID that comes up here from this past Json and remember the Json pass was just that we said the output from perform a bound action we want to do something with it so we can use it dynamically and we do that here in past Json so this word queue item id comes from up here it gets passed here and now we can choose it dynamically so if I scroll a little bit down here we need to find the PATH Json here and we can of course search for it it's right here where queue Item ID so we're going to scroll down or you can search for work queue Item ID that one we will use here then you say show Advanced options and here we need to fill in a couple of things first we have the processing result here we can't do a custom text let's just say the item was processed successfully and then we will give the status process to finalize this item so I'll say processed the status reason that will also be processed now let's click save and we can run the flow once more again let me recap we take an item from the queue and do a little bit of work here and then we'll update it with a little log message and change the status so if I click test here we will run the flow in a few seconds there we go we can click run done and this is done in one or two seconds there we have it I can move back to the queue and right now I will refresh there you go you can now see that our second item got processed and we have a processing node out here the item was processed successfully and let's also inspect this compose automation where you can see here the data 7.4 got processed or seven comma four let us now change the trigger right now we're sitting here and manually pressing a button it could be nice that we will process these Q items as long as we have anything in the queue and we can do that by changing the trigger over here so I click edit instead of this manually trigger flow that means that we are manually starting it of course we will never do that in a production setup but it was nice for this tutorial so here I click delete step what we will do here is to find a trigger move into the database and here you will have that when a row is added modified or delete what you will do is click this drop down and then you say if something gets added again you'll find a work queue items so scroll down here that's it and the scope is the organization that's it now we will save it and we will click test that means that it will listen for New Q items getting added to our queue right now we have of course these static ones in our q and in a little while we will test it so now we will go back to the queue and let me just show you here that it's listening right now go back to the queue because we cannot use this we have changed the trigger now now it will not process these but it will process once we add them so instead of these let me go out here we can say I want to get rid of them and let me show you how that is done most easily that is done by navigating to the queue and see all you can actually idle selecting all of them or for example two let's just select all and then you can say delete delete workq items I'll say delete that means that we have to empty to queue but be careful this could be Q data you don't want to delete it if not needed so right now our queue is empty let's try to add a manually queue here so I'll say new word queue item let's give it the ID 1004. let's say 5.6 like this and we will create it so right now once we have added this we can go to our performer this performer will start in five to ten seconds and while we wait for that you have time to hit the like button of this video this will really help me a lot so please give it a like this video And subscribe to get all my new power automated videos I'll put fast forward to see this flow run there you go and we can see it completed successfully if I go into compose we can see that the data 5.6 got processed and if I go over to my queue there will be no change it will say processed and we can see the item was processed successfully instead of doing this manually we will of course never do that in a production setup item maybe we would add an item or two I don't know but what we'll usually do is to take the data from somewhere that could be a database or an Excel sheet let's create a dispatcher dispatcher is a flow that adds data to the queue so we don't have to do it manually again I'll click these three dots here delete workq item so we have a complete empty queue I'll open up my OneDrive so I go to my file explorer you can do this either in your file explorer or in Microsoft 365 in OneDrive cloud and here I'll navigate to my OneDrive so I'll Zoom to just go in here and this is my OneDrive let's create a new folder for this project feel free to use an already created photos I'll just say new folder and I'll call this live I'll move into the live now we will add data that will get added to the queue so I'll right click new Excel please do this with me you will learn so much let's call it sum.xlsx and open it here we go first I want to rename the sheet it's called Arc one that is in Danish so I right click and let us just say numbers we need some columns let's just stick with the scheme this could be literally any kind of organizational data and usually again you will not create this Excel sheet manually with random numbers but this is a good example so we will stick with that we will need to format it as a table so power automate Cloud can read it Mark your columns and then press Ctrl t on your keyboard and say my table has headers and click OK if you are in the OneDrive Cloud you cannot use the Ctrl T what you will need to do here is to click here and then go over to formatters table that one is present in the cloud so right now we have the table let's also give it a better name than table one because we can inspect the table name what you do is to click inside the table go to table design and instead of table 1 over here let's call it numbers table now we will add some data to it I want like 10 rows so I drag down here in this blue corner like this and the ID that will have the format a thousand and one a thousand and two let's do this right here and then we will have a number a and a number B let's give these random numbers between 0 and 9. so what I can do is I can say equal to random between 0.9 and if you're using semicolon then you just pick a semicolon instead of they're common so right now we're having these random numbers and we can move in over here but right now they are changing because each time I refresh this this will get a new number so what we do here is to mark all of these numbers Ctrl C and then we will say right click save values here and now they are static they are not random anymore these are the numbers that we have to work with we're done with the Excelsior again save and let's exit it let's go out and create a new dispatcher flow in power ultimate Cloud so again to recap we have our numbers then we have the performer here and now we will create a new flow again I can go here I can say Ctrl shift K opening up a new tab so and let's just talk about the structure we have the cues here now we will have the dispatch here and here we will have the performer so that seems the right order and again it's just to make it easier for ourselves here in the development phase go click create again we will have an instant Cloud flow we will just read the sheet by clicking a button but we could have all sorts of triggers such as when an Excel sheet is added to our folder we will run this dispatcher robot all once we receive an email with an Excel sheet attached then we can run it all sorts of nice triggers with fillers let's call this one numbers dispatcher and choose manually trigger flow and click create here we have it first we need to get the data from the Excel table I'll say new step and here I'll find a list rows present in a table go like that the location that will be OneDrive for business document library that is OneDrive now I click this folder over here to select the file remember I created it in live here click this arrow and I find my sum Excel book I also need to pick the table and we renamed it to numbers table so right now we are getting the data from the Excel see let's iterate through it and add each row to our queue so I'll say new Step I'll find an apply to each here and I'll choose the output from here and that is in the value that is a list of items so I click here and now we will go into add an action in this applied to each here so I'll say add a new row again and I'll do that in the Microsoft database yeah so I'm adding a new room and I'll choose the table that will be work queue items again scroll down and find it or write it then we have these things we need to fill in the name of this row we will use the ID for that that was the 1001 1002 and that was if we go back to our queue we used it over here when we added these new Q item we used it as a name but let me go back here and in here I'll just use the ID from the Excel sheet that will be the name of this item and again this is nice so we can easily identify our queue items say that we want to do some exception handling which we will later so please stick we also need a work queue ID here I will have a forward slash then I write work cues I'll have a parenthesis start and end and inside these parentheses I'll paste in the ID of the queue so I go back to the cube find it up here Ctrl C go back here and we will have it here it will look like this click show Advanced options because we also need to specify some input that we want to go into this Q item and here let's just take number a and number B and we can also have the ID tied to it if we want so I can say this comma number a and number B say that we want to work with all these three in our flow I'll do this it's easy to comma separate it because then I can easily split this array when we want to work with it later I also want to say status reason let's put it into acute State because we want to queue it now we will click save here and let's go test this and remember this is the dispatcher this will add the items to the queue so I will say continue I'll run and this one will get rather quick as well that's it if I go back to my queue and then I click refresh in my browser you will see that we now have 10 items here they are immediately getting processed and that is because we have our performance so we add them as cute and now as soon as they hit the queue then our performer will take over which was this and it will set the status to processed and if I go into this performer I go out here to the overview you can see that 30 seconds ago all these transaction ran so far so good but let's imagine that we will face some errors in our automation flow here in the performer let us move in again so I click edit and let's say that we are processing something right now we're only doing our work here in this single action this could be 10 actions it could be child flows it could be desktop flows which I will recommend also using this work queues with so let's try to grab some of the data and create some errors mean purposely errors so let's go to start I like to have a notepad open to make my expressions in so I do this so what do we want to do well here we are taking an item from the queue in the performer and we're parsing it I want to get the input out remember the input let me go back to the queue let me to pick Co the input that is this 1010 for one so I want to take the 4 1 and let's just add them together for a simple automation workflow again this simplify a much more advanced process the process here is not important we just need to understand the dispatcher and form a concept and what we can do with it so this is the mission if I go back to the performer here I go back to the overview here it is and let me just pick one of these items it doesn't matter which I click it so we're going to work with some of the data from the past Json and this is one of the logs from the run you can click this pass Json and down here let's just pick show row outputs this is how it looks if we can see it here we can see we have the body and then we have the input so what we will do here is to take the body from this past Json move into the input and then we have this array here with our data that is the data that we're going to use then we will split it to get the second and third item and remember an array is zero index that means that the first item is index 0 then 1 and 2. that is just programming and we just need to get our head around that so let's create the expression to take out this one and six or whatever the numbers are from the path Json it will look something like this first we will get the second item in the array and I like to make these headers get second item in Array and then I'll do my expression to get the second item in the array let's just start by getting the actual input so what we'll do here is that we will say body and then we'll have a parentheses start and end and a single quotation mark start and end in here I give the name of the action that one is pass Json like this so now I have the body what I will do here is that I will say I need to I'll not have the body I only need the input here you can do that by having a question mark a hard bracket single quotation mark saying input and a hard bracket end so right now we are getting the input out and that will give me this entire row I only need the second item so what we'll do here is to split this and take the second item so I'll go down here and I'll say out here I'll say split I will have a parenthesis start I'll have a comment two single quotation marks like this and I'll say split by a comma so right now what this will give me is the second item that is um this will not give me a second item this will get an array of all three items now I want the second item and what I'll do here is just say question mark hard bracket and again this was CEO indexed so I will say index one that will give me the second item which is one here similarly I will get the third item in Array and what I'll do here just copy this paste it in here so to get the third item that will be index two and that was just also of the header so I'm creating these Expressions here instead of creating them in the expression editor in power automate I always go with that I can easily save this to have it when I develop later or tomorrow although this comes out you can see these numbers it will come out in a string format so we need to convert it and it's because we will add the two numbers together that will be our automation work so I go down here then I'll say convert second item to indica and what I'll do is just go up here Ctrl C I'll have an INT do like this that will convert this to an integer similarly I will convert the third item to an integer so I'll say convert third item to an integer move here and that will be two now we can add these two together and I like to do it in steps because that makes it a lot more easy so add two integers to each other and feel free to just write in the expression if you're comfortable with that here I'll say add and then I can say the first one here and just copy paste it in I'll have a comma and then I'll have my second summer end here and I will have a parentheses end that is my expression to add these two together and again in Z the steps here we're getting the items we're converting them to integers and now we can add them together here with the add function mark this and copy it out Ctrl C then I go back to my flow and I click edit what we will do here is to create it in the compose automation work so instead of this the data input got processed I will delete it and I'll go to expression and Ctrl V paste in my expression I'll click OK we now try to add the two things together and that will work but let's create some forced arrows so I'll click save here that will save my flow and navigate back to the Excel sheet that was called sum xlsx here we will have our errors I'll just write in some random numbers x y set so what you see here is that we can do we can process this process this this will give an arrow we can process this this Arrow here process process error process so of course we cannot add 9 to set for example but we'll see let's try to run it so again I can just close this Excel one here so what we'll do here this is the performer we need to add something to the queue we'll do that by the dispatcher I'll go to my queue and again I mean I just for Simplicity let's empty it so we only have the queue items that we'll be working at right now in a real life scenario you will just have it all locked here in the queue but for development for now it's great we have the dispatcher here let's go back to the overview close this and click run and now we're trying to add 10 more items to the queue that will go well but once we start processing them three of these items will not go well and that will actually prevent the Q items from reaching the process State and that's why we need to do some error handling and let me just refresh here I'm sure it's over now we added it if I go to my performer I can go back here we can see that we have three of them failed we knew that and if I go back to the queue I refresh it here you can see that we have processed everything except these things here with an X Y set in it so that is not really nice I really want to say this has an arrow this has an arrow has narrow because then I can easily see when I expect the queue that we had some problems here go back to the performer this was the last one here I will click edit flow and here in the beginning I want a transaction status because I'll use that for error handling a little bit below so say insert a new Step add an action and here you will say initialize variable pick that one here I will call this transaction status transaction status like this it will be of the type string and right now we will give it a null value you do that by clicking here go to expression say null and click OK it's not inverted just do it twice if you if it asks you to so this one this value is null that means that it's not empty but it's simply not there we will add things to it once we move further ahead so after the past Jason and that is because I want to say does this make sense I can let me collapses but I want to say can I add these two numbers together if not I want to log one transaction status here and if I can I want to log a success to this transaction status and I can also rename this up here so it makes a little bit more sense Three Dots here I can say transaction status like this click enter and then do this now we can see easily see what's going on and here I want to introduce you for the concept try catch this will not be a track catch lesson if you want a full lesson on error handling handle exceptions here in power automate I can made a complete tutorial on that click the video in the upper right corner let's create a simple error handling for this and we will introduce the try catch so right after the pass Json click here add an action find a scope a scope is simply just a container where we can bucket in other actions it will be ignored at runtime this outer scope but the actions inside will be performed let's go rename this so this will be a try here we try something in and what are we going to try we're going to try this compose automation work and let me just drag it here and if this fails we want to perform an another scope so I click plus here I'll say add an action I'll find a scope here and let's rename this to catch like this so now I want to say if this one fails up here I want to run this catch I can do that by clicking these three dots configure run after I will not have it successfully because I only want whenever it fails up here so I'll say done now this runs and whenever we have an arrow up here and I also want to say I want to go to the updated row whatever happens here in the catch and let me recap this if this goes well this will simply just go down here because we defined that this shouldn't run whenever this was successful again let me show you can see we untick that so similarly I'll go here to this tree dots and this is a very powerful way of handling errors so whatever happened here I will go to this update a row now also let's update the transaction status because we're going to use that here in the update row and let me show you where we want to have it here we want to have a processed or an error here processed error and we're going to enter custom value that is the way where we can customize this on Dynamic input and this needs a number where 2 is a process and four is an arrow so we will lock these two numbers into our transaction status and use them down here as a custom value go up here so whenever this goes well we can go to the next action when this fails we know that this next action will not be performed so we can click add an action I can say set variables sorry set variable here variable yeah it is and I want to set the transaction status and I want to give it the transaction status to that was the success or processed let me just rename this for Simplicity transaction status to just so I can see what's going on it literally means nothing it's just for the overview similarly here in the catch I'll have add an action set variable again I'll have the transaction status and now I will give it the value 4. click the three dots rename transaction status for like this now we can use it down here in the update a row click it here so what we will do is not use the processed and instead I can delete this and instead use the custom value here so if I go over here now I want to use the transaction status it might be that you can't find it out here I cannot see it here so what we will do is simply just go into expressions here you will see variables now we will just make an expression for that to call that variable and let me just move in here I will say trans action status I always like to control a control C to copy this because sometimes they do disappear now it's here but I can also use it down here click this drop down enter a custom value and again we cannot find it so I'll just Ctrl V paste it in here and now you can see it says an error it's not it's there so now we have error handling and let me show you how powerful this is so while I save this let's go to the queue let's clean the queue again again let me just do it so we can see what's going on here in our queue because right now we have done some Advanced exception handling we will start our Flow by running the dispatcher that will add the items to the queue and then our performer now with error handling takes over so again I can just refresh here this will take like eight seconds or it did before now we succeeded I can go to my performer and go back here and here you can see that we ran eight minutes ago so let me recap refresh this it will take a little what here we have it now you can see everything succeeded and that was because we made error handling yes we have three errors in there but that's fine our automation workflow is now able to handle those and let's see how it looks in the queue Let me refresh the queue there you go we can now see that this one here X Y set now have the status exception you can also have another processing node instead of this item must processed successfully we can have a customized nodes but so far they got processed successfully but there was a status of an exception that is fine that could be that it's an exception in our data that could be the business department of our company gave us wrong data and then this item was processed but we just had some exception in it today we talked about exception handling and the try catch in power ultimate now you should go watch this video about exception handling and the try catch in power ultimate if you want to network or solve problems with more than 7 000 automation developers then you should join the Isle of automation Discord it's completely free and your invitation and guide is right up here

Channel: Anders Jensen

Views: 19,214

Keywords: power automate, work queue, power automate tutorial, work queues in power automate, queue power automate, dispatcher power automate, performer power automate, tutorial, queue items power automate

Id: k7H7kBRI4-M

Length: 42min 18sec (2538 seconds)

Published: Thu May 25 2023