Mastering Power Automate: A Comprehensive Guide to Flattening Nested Arrays #PowerAutomate #Tutorial

hi there folks in today's demonstration I'm going to show you how to flatten or explode nested arrays in power automate on scale so that it's efficient using my favorite action select and some string manipulation techniques so you can see on screen right now on the left hand side I have an array and that has nested arrays with product IDs and ultimately on the right hand side I have a new array that contains an object per product ID so that John who has two product IDs has now got two objects so traditionally you'd use nested apply to eaches but I'm going to show you some Advanced Techniques in the form of today's demonstration so if you haven't already please make sure you like And subscribe and without further Ado let's jump into the demonstration so here I am in power automate and at the top of my flow I have a compose action and that has a data structure and if we pop open the raw inputs we can see the data structure it's an array it includes object or multiple objects of which each one is for an order so I have the customer name I have the order number but then within that I have a nested array which is the product IDs array and there are two product IDs in this particular array now ultimately what I'd like to try and do is to explode or flatten this nested array so that I get an object per product ID so if we look at Jane as another example she has three product IDs therefore I'd expect to get three individual objects and then if I was to use that data I could have a CSV file that would have three lines on there for each of those product IDs or if I was creating a row in a table in Excel I would have three individual rows in that table now in terms of what that data should look like if I move further down my flow I have a compose action here and if I pop that open you can see the data structure so John Doe he had two products therefore there are now two objects we've got Jane Smith she had three product it therefore she's got three objects and I'm going to demonstrate to you two different techniques the first of which is down the left hand side a traditional technique using nested apply to eaches and then down the right hand side using some string manipulation and one of my favorite actions select there are 1 2 3 4 five six actions to achieve this flattening or exploding of data and what that means is by comparison the left hand side is going to grow in terms of the number of API C when you run it on a larger data set and also probably exponentially grow for the number of minutes hours that this flow could potentially run whereas on the right hand side using those six actions it is going to work across massive data sets and only require those six actions so it'll never grow in terms of the number of API calls that are required so in order to put that into a bit more context I have an Excel file and I have that same data structure I have 10 records of which John do has has two product IDs and James Smith has three and I'm going to try and turn that data into a structure like this so that everyone has a row per product ID so John has got two rows Jan Smith has got three rows now you'll see on the right hand side I I also have a table in Excel I'm going to write the final data output to this Excel table when I run that flow to demonstrate it working so back onto power automate if we have a look at the history of this flow you can see that in order to Loop through through those 10 individual orders it took 9 seconds so it didn't take too long but if we have a look at the right hand side all those actions currently are showing 0 seconds so rather efficient using that technique so if I go in to edit that flow I have a compose at the top and that has my data structure this could be from data verse it could be from SharePoint it could be from a third party API it doesn't really matter but I've made it a fixed data source at the moment and used a compose for the purpose of the demo I've used par Json based on that data structure that's in the compose and then I've initialized a variable and I've called that order line for each product then in order to understand the apply to each for each of the orders within the parse Json in this case I'm going to have a nested apply to each and then have a look at all the different product IDs within each of those orders and that's why we have these nested apply to eaches which ultimately enables me to append to the array that I've created using values from the customer name for each order the order number again from each order and then the product ID which is based on each product ID within that second nested apply to each Loop so if we have 10 orders and there's a multitude of different order or product lines we're going to have 26 instances of this Loop running in in the form of nesting and that results in 26 API calls the compose is just the output of the variable so I could check that the output was as desired so it's a rather straightforward flow something you'll probably see an awful lot of but if your data structure is to grow in size and you're using thousands or tens of thousands of objects within a data structure this is going to consume an awful lot of API calls it's going to take an awful long time and you'll probably end up losing a lot of hair as a result now if we jump back up to the top of the flow you'll see that over on the right hand side I've got the history so we can explore that whilst looking at the right hand side method for achieving this with six actions starting from the top I have that original compos that has my array but then if we go down onto the first action within that right hand side branch we have a compose which I've called array with string and it literally is an array with a string it's just a string that needs removed and we need that string in an array for the technique that I'm going to demonstrate and hopefully all will make sense as we move through these six actions so if we look at that from a history point of view you can see it is literally just an array with a string and I've called that a string that needs to be removed you can be creative as required the next action is going to take the original array which is the outputs from the compose and then I'm using an expression called set property and so what set property does is it takes an object as the first property it takes the name of a key within that object in this case product IDs and then it replaces it with a value and I'm replacing it with a union of the product IDs which is item product IDs and the outputs of the compos array with string so if we have a look at that from the history point of view and pop that open we can see that the original input we have our array which is John his order number and product IDs of which there are currently two but then if we look at the output we can now see that we've added just a string that needs to be removed into that product's ID array and this is is the useful piece that we're going to use in the next action as I scroll down we can see that Jane is the same she's now got four product IDs and then Bob has got three because originally he had two product IDs so onto our next action it is another select and this is a very efficient way of transforming or manipulating data it's looping through all of those objects and so the input is the body of the select with the array with the string that's been added added and then the the expression again using text mode is based on this time a join so what a join does is a join takes an array of values and it joins it based on a string or value that you provide there are two properties so I'm taking the product IDs of which John now has three because we've added that string that just needs to be removed into his original product IDs array and then we're adding to that a concatenation of of some pipes in this case two pipes at the start four pipes at the end and this is important also and I'll explain later on but in the middle we are using remove property and we're removing the products ID's property from the original object so the original object contains the customer name the order number and also the product IDs as an array but I'm actually removing that now so that I don't have the product ID in this particular data sample because I'm going to read it in one of the next actions but based on each of the individual product IDs now if we look at that in terms of the history and have a look at the select we'll be able to see the input which is our output of the select from above of course that includes our customer name the order number and the product IDs with are just a string that needs to be removed but you'll see the output is far more complex now and so what's happened is it's created a string now the best way I can describe this is you might use a join if you're concatenating email addresses and inserting a semicolon between it so what we've done here is we've taken the individual order number and then we've joined it with two pipes and then the original object of the name of the customer the order number and then we do that again with four pipes and add in their next order number again with two pipes and their customer name and their order number and then you'll see we have that final one four pipes and then just a string that needs to be removed and the reason that we do this is join does not insert the value that you're looking to join with at the end of the string it only does it in between all of the values within an array so if we did a join with just two objects in this case with John product ID 1 2 3 4 5 and then 2 3 4 56 we'd only join in the middle but because we've now got three products We join in the middle middle of the 1 2 3 4 5 the 2 3 4 5 6 and the 2 3 4 5 6 and just a string that needs to be removed again hopefully all will become clear as we move through this flow now the other thing to call out that's very important is I use two pipes in the middle of of the join and that is to enable me to split both the ID from the original object but I also use four pipes at the end here and that marks the end of each object in our join so you'll see that in the string and probably the best way for me to explain this now is to open up this into something like notepad and break it down so I'll copy the output here and with it popped into notepad we can see the data structure that's being created so we still have 10 individual objects in an array but within each of those objects now we have a string concatenation by using that join and that string concatenation is split by four pipes so I'm highlighting four pipes there if I do a contrl c and crlf and paste we can work our way through each of those four pipes and you can see where they are they're at the end of or in between each of the individual objects so if I highlight an object on screen right now starting with the order number and moving all the way to the end here we can see with the next four pipes appear and we're going to use those four pipes to our advantage to split this string again back on the four pipes once it's split on those four pipes we can then split on these two pipes that we have within those new strings and that will enable us to get the order number and insert it back into this object that we have created as a result of doing this join so each of these order numbers that we see which are all unique for instance this op 8912 this QR 90123 and also this ST1 1 2 3 4 we're going to insert them back into each of their respective objects using a select and then the last value of each of these strings which is just a string that needs to be removed we will of course remove using a filter array so back over onto power automate I'll just make my way down through this flow and we'll move both left and right so we're going to have a look at a join and that join is going to be based on that select and we're joining with four pipes PES now we've already joined with four pipes within our select but we have 10 different strings within that structure that we created if we now join with four pipes we're going to end up with one massive long string of all of those values so if we have a look at the output of the join we can see the input which is an array we're asking that array to join based on those four pipes but if we look at the raw output and I'll copy this into notepad if if I paste that in you can see now we have just one massive long string and actually if I enable word wrap we'll be able to see what it looks like this one string which is based on the body is all of the 10 different objects that we had before but now with four pipes inserted at the end of just a string that needs to be removed and if I highlight there you can see there are four pipes and there are another four pipes and so with all of this joined together we can now split again on the four pipes and rather than get back to the 10 original objects we'll get back to 26 individual objects because we've created the individual objects here with four pipes splitting the nesting or the explosion or flattening of their arrays using four pipes we can then split by those four pipes and get back 26 individual objects so next up we're going to a look at filter array and we're doing a split on the body of the join based on four pipes that will give us 26 objects but within those 26 objects there will also be a number or actually there'll be 36 objects there'll be 10 different strings that we need to remove that are based on just a string that needs removed so I'm using a filter array based on item open close brackets is not equal to just a string that is removed and I used a string here in that original array rather than an empty array because I thought it made it easier to understand and also easier to find so I can easily remove those strings from the array in order to make sense of this if I jump across onto the history we can have a look at the input which is based on that split and if I pop open the input we can see we have that array of all of those strings and just a string that needs removed so after each of the customers you'll see there is just a string that is removed as we work our way through that array so at the moment these are just strings they're stringified Json and we can turn them back into Json but what the filter array is doing is taking that split based on those four pipes and then we are filtering out just a string that needs to be removed and so if we look at the raw outputs we can see now we just have those 26 individual strings including those order numbers and also the customer names so finally we jump across into our last select our sixth action we're using the body of the filter array as a from we're in text mode again and using add property and this time what we're doing is we've got a combination of Expressions here but we're splitting the string that we saw in the history which in fact if I reopen as the output from the filter array we are splitting on these two pipes by using this expression here split on the two pipes and because we can then select the values which are either on the left hand side or the right hand side they're referred to either integer value one or zero so the first one is zero the second one is one and then we're able to create or update the original object with the customer ID and so we're able to add a new key value into the original object which includes the customer Custer name and also their order number based on this split so by splitting we're able to get the second value here we're using the integer one I'm converting that into Json because it is a stringified Json and then going to use add property which is going to take that stringified or now converted Json adding it in a property called product ID and then I'm going to use split once more but to get the first value which is integer zero which is our order number and it will add it into that object and give us 26 individual objects with those customer names and of course their product IDs if we go and have a look at the select and the history we can see the raw input again that contains all 26 individual strings and then because we're doing that split and also then adding a property into the Json version of this customer name value that we have here if we look at the raw output we can see that we now have a lovely array of customer name customer orders and product IDs and John has two Jane has three and Bob has two now in order to complete things I then have an add a row for each of the objects within the select so the body of the new select is the input the apply to each and then it in the add a row into a table I've simply used expressions for the customer name the order number and then the product ID and that's of course based on the structure of the array that I've created and I am then adding a row into the table so if we save and we test this flow we can watch the flow unfold we can see that the actions on the right hand side have actually already completed and I'm already writing that data to excel whereas on the left hand side the nested looping has is finally completed after 13 seconds and of course this will grow exponentially based on your data set but if we jump across onto my exploding data we can see that those individual rows are being written to the Excel table as expected and matching that sample data set that I created prior to my demonstration so I'm hoping that I've demonstrated to you some Advanced Techniques in power automate in order to manipulate the data stringifying it and then converting it back into Json but by doing so we've achieved the same thing that's taken potentially 26 or more API calls based on the size of your data but six actions that will be scalable can work on large data sets and in fact I've seen it being used on the graph API where there were tens of thousands of Records being brought back there was a requirement to flatten that data and where it took 20 or 30 minutes before it was taking 10 seconds us using this new technique so something to explore if you've not tried before it's definitely an efficient technique and if you're looking to write data to excel in bulk do not look at using a method like this with an apply to each go and have a look at one of my videos on how to write data to excel efficiently using either graph API or office scripts the last thing to call out with this new array that is created if we have a look at that raw output with the product IDs for instance the could be a need to relate that data to the details of those individual products based on the product ID there is also an efficient way to join arrays and again I have a video on joining arrays efficiently without using an apply to each so not only could you flatten your data or explode your data using this technique you could then join your data using one of my other videos and achieve some transformation in power automate using very minimal number of actions but also very efficiently using the number of API calls that you have available to you in your license so as always I hope you've enjoyed this demonstration thanks very much for watching to the end if you have any thoughts or feedback please do leave them below or any more challenges do let me know but otherwise thanks for watching and I hope to see you again sometime soon cheers

Channel: DamoBird365

Views: 2,066

Keywords: power automate, Power Platform tutorial, power platform, power automate advanced

Id: _93_aJu44zE

Length: 21min 12sec (1272 seconds)

Published: Sat Apr 27 2024