Convert Excel to Json with Nested Array

[jsbev]

Hi, 

I have the below table in an excel file and want to convert to json with nexted array Properties as shown in the example below. 

How can I achieve this please?

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:

[  
  {
        "OwnerID": "L001",
		"Properties": [
				{
					"PropertyID": "P001",
					"Property Address": "12 St LA",
					"Rent": "300",
					"Bedroom": "2"
				},
				{
					"PropertyID": "P002",
					"Property Address": "3 St SA",
					"Rent": "450",
					"Bedroom": "3"
				}
			]
	},
    {
        "OwnerID": "L002",
        "Properties": [
				{
					"PropertyID": "P003",
					"Property Address": "4 St SA",
					"Rent": "500",
					"Bedroom": "4"
				
				}
			]
	},
    {
        "OwnerID": "L003",
        "Properties": [
				{
					"PropertyID": "P004",
					"Property Address": "20 St LA",
					"Rent": "400",
					"Bedroom": "2"
				}
			]
	}
]

Thank you

[schwibach]

First, you convert the Excel sheet into a table.

In the flow:

- you create an array from your OwnerID Column (Initialise array variable 'OwnerID' and then append to array variable actions)

- you initialise an array variable 'result'

- Then you use the union expression to join the array with itself so you only get the distinct values

- Then you loop through the array with a For_Each loop and filter your 'List rows in a table' Excel action for the owner id

- Then you use the Append to array action on the 'result' array and give it enter your {} object with the dynamic values you get from Excel.

In the end put a compose action where that array is supposed to go, so you can check it.

It can be challenging if you've never done this, the key things are to work with the Append_to_array_variable action, the union expression for the distinct values and being able to loop through an array with the For_each action.

[grantjenkins · Accepted solution]

I've built a flow that gets what you're after.

The Excel Table is shown below. The name of the Table is PropertyTable.

See full flow below. I'll go into each of the actions.

I initialize two variables.

output of type array that will contain the final output.

owners of type array that will hold each of the owners.

List rows present in a table gets the Excel items.

Next, we loop over the rows and append each OwnerID to the owners array. This will contain duplicate OwnerIDs which we'll remove in the next step.

We can use a union expression to remove duplicates, so we have a unique list of OwnerIDs. The expression we use is.

union(variables('owners'),variables('owners'))

Now we can loop over each of the unique owners (using output from Compose Unique Owners). Within the loop we filter the items returned from Excel for each owner, then select each of the owner's properties, and build up our objects. The full Apply to each owner is below:

Filter array takes in the value from List rows present in a table, and filters on the OwnerID equal to the Current Item.

Next, we use a Select to get the property details for the owner using the output of the Filter array.

The expressions used are:

item()?['PropertyID']
item()?['Property Address']
item()?['Rent']
item()?['Bedroom']

We then build up the owner object using the Current Item (OwnerID) from the Apply to each owner loop, and the output of Select Properties.

Then we append the owner object to the output array variable.

To view the final output, I've added a Compose outside all the loops to show the contents of the output array variable.

Any questions - don't hesitate to ask.

[jsbev]

Thanks Grant. I'm getting the below error at Apply to Owner step. I have reviewed the steps couple of times and not sure what I am doing wrong

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The inputs of template action 'Filter_array' at line '1 and column '3079' is invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of action 'Filter_array' to be referenced by 'repeatItems' or 'items' functions.'.'.

[jsbev]

Was able to resolve the above error by selecting the Current item under Apply to each owner

Now getting the below error

[jsbev]

All good, my mistake, i forgot to add the below in the expression

union(variables('owners'),variables('owners'))