Mastering Power Automate: A Comprehensive Guide to Flattening Nested Arrays

[@ricardobunge5957]

This is absolutely BRILLIANT! We just had a use-case last week with nested loops - looking forward to using this technique to banish such abominations... 😂 In all seriousness, though, thanks for sharing. I don't know how the inspiration comes to you for these things, but please keep them coming!

[@DamoBird365]

Sometimes I’m lucky and someone sets me the challenge. This is one of those moments 😉 thank you.

[@Untethered365]

So fun and the join with pipes is very creative!

[@mikaeldias5372]

Brilliant... As usual 👍

[@jamesclark2020]

😀 Awesome video Damien! I love Select!

[@keithbrown6389]

Thanks for always showing us a faster way! I'm going to have to rebuild a few flows...

[@DamoBird365]

Let me know how it goes please 👍

[@geralddahl9159]

I’ve come across string outputs I’ve created that have all those backslash ( \ ) characters and then I would as a result use a combination of split and replace to remove them ahead of having elements reset into arrays. I would be grateful to understand more about why the backslashes appear in the first place and how to more efficiently deal with them - I believe you used json in one of your steps to fix/resolve the issue?

[@DamoBird365]

That’s correct. /“ is when the json is a string. If you wrap it in json() it will validate the string as json learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#json

Liked and Subscribed👍 👏🔥🚀 Thanks Damien!

[@garypotter4273]

Very well presented!!!

[@DamoBird365]

Cheers Gary 👍 very much appreciated.

[@TheCarter117Gaming]

Love it. This is similar to what I messaged you about on reddit. Now you should do a video on when you need to pull the nest array values for a multi-lookup sharepoint column; when sharepoint has all that ID, Value, etc and you want to flatten just the value.

[@DamoBird365]

@@TheCarter117Gaming hello 👋 would that be get items? I reckon xpath would be your best bet maybe?

[@TheCarter117Gaming]

@@DamoBird365 i ended up figuring something out using select actions after doing a get items action. Ill have to take a look at what I did and let you know. I am thinking how I could make it even more efficient with a string manipulation like you did in this video. But as an example… lets say you have a sharepoint list of a asset inventory that is three columns: Hostname (single line text) IP Address (single line text) Installed Software (multi-look up) You would do a get items > select to map the three fields where for the multi lookup column you select the dynamic content of the installed software array (which has all the stuff in the multi-lookup array, as it doesnt let you just select the value elements within the array). The output would like like: [ { “Hostname”: “TESTSERVER01”, “IP Address”: “192.168.0.3”, “Installed Software”: [ { “ID”: 1, “Value”: “WinZip v1.0” }, { “ID”: 5, “Value”: “League of Lengends v13.8” } ] } ] The output you want to get to is: [ { “Hostname”: “TESTSERVER01”, “IP Address”: “192.168.0.3”, “Installed Software”: “WinZip v1.0, League of Lengends v13.8” }, {…} ]

[@TheCarter117Gaming]

@@DamoBird365yea, its manipulating get items. A lot of times in Power automate especially while working with SharePoint, it is rare to just have an array with strings by themselves.

[@TheCarter117Gaming]

@@DamoBird365how would I do it with xpath?

[@DamoBird365]

@@TheCarter117Gaming I think I demo that method here: Boost Your Power Automate Skills with Complex Arrays, Select, XML, and Join Techniques kzbin.info/www/bejne/l5fUp3p3l5djetk

[@oyvus]

Holy smokes. I was thinking about writing to you with some entangled JSON, but now I don't have to. That is excellent service 13/10 for that service 😅

[@DamoBird365]

😂 let me know how you got on.

[@igortorrealba]

Amazing! I just ran my flow with nested arrays and it failed miserably, but while running I had time to watch your entire video (which clearly makes a point) and will gear up to apply the same process. I have one little issue to kill first, my field equivalent to your product IDs is not an array, but a multiline string, which basically has " " s at the end of each line and another one for a blank space. So I will first try to convert that into an array (with a loop...) and then apply your methods (unless you have an advise :))

[@DamoBird365]

You could try split()?

[@nicolasf4162]

Finally someone who is bringing a bit of engineering ! I have a question, I would you proceed if your product IDs array is an array of objects and not an array of strings ? So flattening an extra level ? Recursively ?

[@DamoBird365]

You could look at using XML?

[@nicolasf4162]

@@DamoBird365 Did not have time to watch all your videos, seems could to the job indeed. Again, crystal clear =)

[@kumarirakhi75]

I have a flow for outstanding actions for some users. Using Office functions, I take user profile and then get Manager. Further I create the HTML table using filter array. at the end when I try to send email to managers. Only the first or last is being picked up. Can you help me with the function that picks right manager of each user profile to send email?

[@noodlebrains]

Great video, I was looking at a way to combine the data from an apply to each loop into a new array of data which can be used in the update a row action (since each sheet in the apply to each loop has a different column and needs to merge into a main sheet with all columns). The apply to each loop to update row is currently deleting data if the column doesn’t exist in one of the files in the loop. So can I do an append to variable linked to current item like you did 4min into video and use a combined new array to update row

[@DamoBird365]

It sounds like your excel sheet is missing a column? Excel with only update the columns that exist. Have you seen office scripts or graph api? Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate kzbin.info/www/bejne/naXPnJ-wnpuAesk

[@noodlebrains]

That’s very restrictive of Power Automate not to have the ability to have data with different column names entering into a main sheet with all the names. I’m not sure that video will work, all my sheets have tables with the same name. If the sheets combine, the tables would need to combine as well - so that all data is consolidated into one table. The table names are fixed. The only solution I found so far is merge via Power Query, but that would take forever.

[@DamoBird365]

I think you’d be better sharing your requirements on a forum with screenshots as I don’t fully understand your requirement. Excel won’t add new columns to a table, but you can map different keys to an existing column if required.

[@ALearner-9852]

Hello Damien, Thank you for this fantastic video! I have a question: Does this approach work if there are multiple objects within the array? In my scenario, I need to flatten two objects. I followed your excellent tutorial, and it worked perfectly for the first object. However, when I applied the same method to the second object, it didn’t produce the expected results. Is there something different I need to do in this case?

[@DamoBird365]

This is best shared on a forum as I cannot visualise your problem. I hope you can work it out and share your findings.

[@ALearner-9852]

@DamoBird365, Thanks for the reply in your busy time. Here’s an example JSON structure for my scenario: ```json { "Salutation": "Mr", "FirstName": "John", "LastName": "Cena", "Department": "IT", "Achievements": [ "test1", "test2" ], "Rewards": [ "Test", "Test", "Test" ] } ``` I hope this gives you a clear picture of what I’m working with. The challenge is flattening the nested arrays. In some cases, there might be more than two arrays. It would be great if you could suggest some solutions for this.

[@DamoBird365]

You could just use join on both of them to create a string. Inside a select. Addproperty. Join(achievements,’,’). Check this out kzbin.info/www/bejne/fWS0Yoqrj7mKq7s