You’re offline. This is a read only version of the page.
Hi,
I have the below table in an excel file and want to convert to json with nested array Properties as shown in the example below.
How can I achieve this please through an automated flow?
Excel Data:
| OwnerID | PropertyID | Property Address | Rent | Bedroom |
| L001 | P001 | 12 St LA | 300 | 2 |
| L001 | P002 | 3 St SA | 450 | 3 |
| L002 | P003 | 4 St SA | 500 | 4 |
| L003 | P004 | 20 St LA | 400 | 2 |
Output Required in Json:
Also, can you check that the input into your Apply to each is using the output from the Compose and not the Select.
I don't think that's the issue actually.
It seems as though your Name value isn't coming through. Can you run the flow again and have a look at the output from your List rows present in a table, and the following Select. I wonder if your Name column in Excel is coming through with a different header (maybe not called Name).
In your first Select can you change the Header from Id to id (lowercase i) to see if that fixes it.
Here's the flow inputs:
union(body('Select'),body('Select'))
items('Apply_to_each')?['id']
item()?['PropertyID']
Thank You.
Are you able to send through screenshots of your flow to see what you have put for each of the inputs, etc.?
Below is the output I get:
Share
Report
All responses (
Answers (
{
"owner1": {
"id": "1",
"property": [
{
"PropertyID": "P001",
"Address": "12 st LA",
"No of Bedrooms": "2"
},
{
"PropertyID": "P002",
"Address": "14 Louis st LA",
"No of Bedrooms": "2"
}
]
},
"owner2": {
"id": "2",
"property": [
{
"PropertyID": "P003",
"Address": "15 Merry st LA",
"No of Bedrooms": "3"
},
{
"PropertyID": "P004",
"Address": "29 John st LA",
"No of Bedrooms": "2"
}
]
},
"owner3": {
"id": "3",
"property": [
{
"PropertyID": "P005",
"Address": "8 Grant st LA",
"No of Bedrooms": "3"
}
]
}
}
Thanks for your help, I have tried it and it is working, but I am not getting the Json code as required and not even getting the full Code.
Following Excel table:
Name id PropertyID Address Market rent No of Bedrooms
Output got :
This should get you what you want. The only thing you'll need to do is add your additional columns to the Select action.
I've got the following Excel Table for this example:
The full flow is below. I'll go into each of the actions.
List rows present in a table retrieves your Table data.
Select retrieves just the Name and Id properties which we'll use to loop through later.
Compose uses a union expression to remove any duplicate values from Select as we only want one instance of each owner to loop through. The expression here is:
union(body('Select'),body('Select'))
I then create a string variable called output that will hold the final output once we combine our owners, properties, etc. I set the initial value to {
The Apply to each uses the output from our Compose since we want to loop through each unique owner.
Filter array returns rows that match the current owner's Id using the following expression:
items('Apply_to_each')?['Id']
Select Properties takes the output from Filter array and allows us to specify exactly what properties we want to include. You'd need to add your additional fields here. We don't include Name and Id here as we already have them outside the objects. The expressions used here are:
item()?['PropertyID']
item()?['Address']
item()?['Number of Bedrooms']
Compose Build then concatenates all of our data to build up the JSON array you're after. You should be able to just copy the expression below if you've named all your actions the same as what I have.
concat('"', items('Apply_to_each')?['Name'], '":{"id":"', items('Apply_to_each')?['Id'], '","property":', body('Select_Properties'), '},')
I then append that output to the output string variable.
Then outside the Apply to each we do the following:
We append a closing } to the output string variable.
And finally, we convert the output to JSON using a json expression.
json(variables('output'))
I don't think this will help me in that way, I want this in a Microsoft power flow, can you help me in that, please.
#1
David_MA
140
Super User 2025 Season 1
#2
Michael E. Gernaey
138
Super User 2025 Season 1
#3
stampcoin
129
