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!
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 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.
@@TheCarter117Gaming I think I demo that method here: Boost Your Power Automate Skills with Complex Arrays, Select, XML, and Join Techniques ua-cam.com/video/afqvGAb20Dw/v-deo.html
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
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 ua-cam.com/video/gtlklzi6MDg/v-deo.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.
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.
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
ua-cam.com/video/afqvGAb20Dw/v-deo.html
Brilliant... As usual 👍
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
So fun and the join with pipes is very creative!
😀 Awesome video Damien! I love Select!
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.
Very well presented!!!
Cheers Gary 👍 very much appreciated.
Liked and Subscribed👍 👏🔥🚀 Thanks Damien!
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
ua-cam.com/video/gtlklzi6MDg/v-deo.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.