Video není dostupné.
Omlouváme se.
Mastering Power Automate: A Comprehensive Guide to Flattening Nested Arrays
Vložit
- čas přidán 6. 08. 2024
- Welcome to today's tutorial on how to efficiently flatten or explode nested arrays in Power Automate. In this video, we'll cover advanced techniques that involve the use of my favourite action, "Select," and some string manipulation methods.
We'll start by understanding the structure of a nested array and how we can transform it into a new array, where each object corresponds to a unique product ID. Traditionally, this process would involve using nested 'apply to each' loops, but we'll explore a more efficient approach that can handle large data sets without consuming excessive API calls.
During the course of the video, I will demonstrate two different techniques: a traditional one using nested apply to eaches and a more advanced one using string manipulation and the select action. The latter technique only requires six actions and is scalable to work across massive data sets.
Finally, I will show you how this data can be written into an Excel table, ensuring the correct transformation and organization of the data.
Remember, this technique can also be used with data from various sources such as Dataverse, SharePoint, or third-party APIs.
[00:00] Introduction: Overview of the tutorial on how to efficiently flatten or explode nested arrays in Power Automate.
[00:49] Understanding the requirements of a nested array: Dive into the structure of a nested array and discuss the transformation process into a new array where each object corresponds to a unique product ID.
[03:42] A basic flow with nested apply to each: Demonstration of the traditional method of using nested 'apply to each' loops to flatten or explode arrays.
[05:23] Using Select and stringified JSON for an efficient flow: Introduction to a more advanced technique using the Select action and string manipulation methods. This technique only requires six actions and is scalable to work across massive data sets.
[17:40] Writing data to Excel: Show how the transformed data can be written into an Excel table, ensuring the correct organization of the data.
[18:50] Learn about writing data to Excel efficiently: Discussion on how to write data to excel efficiently using either the Graph API or Office Scripts.
[19:50] Join data efficiently: Introduction to an efficient way to join arrays without using an apply to each loop.
[20:30] Outro: Wrapping up the tutorial, encouraging viewers to like, subscribe, and leave their thoughts in the comment section.
Join Arrays Efficiently • Power Automate Join or...
Write to Excel Efficiently • Create Excel File and ...
Complex Arrays • Boost Your Power Autom...
Got an idea? Or video request? Drop me a note here forms.office.com/r/4EqE7VHVfH 👍
Want to buy me a coffee www.buymeacoffee.com/DamoBird365 ☕
#PowerAutomate #JSON #PowerPlatform #Tutorial #Efficiency
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!
Sometimes I’m lucky and someone sets me the challenge. This is one of those moments 😉 thank you.
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 😅
😂 let me know how you got on.
Brilliant... As usual 👍
So fun and the join with pipes is very creative!
😀 Awesome video Damien! I love Select!
Thanks for always showing us a faster way! I'm going to have to rebuild a few flows...
Let me know how it goes please 👍
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?
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
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.
@@TheCarter117Gaming hello 👋 would that be get items? I reckon xpath would be your best bet maybe?
@@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”
},
{…}
]
@@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.
@@DamoBird365how would I do it with xpath?
@@TheCarter117Gaming I think I demo that method here: Boost Your Power Automate Skills with Complex Arrays, Select, XML, and Join Techniques
czcams.com/video/afqvGAb20Dw/video.html
Liked and Subscribed👍 👏🔥🚀 Thanks Damien!
Very well presented!!!
Cheers Gary 👍 very much appreciated.
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
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
czcams.com/video/gtlklzi6MDg/video.html
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.
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.