Convert Excel to Json

[garry9318]

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:

{

    "owner1":{

        "id":"L001",

        "property":[ {

            "propertyID":"P001",

            "Property address":"12 st LA",  

            "Rent":"$300",

            "Bedroom":"2"

        },

        {

            "propertyID":"P002",

            "Property address":"3 st LA",  

            "Rent":"$450",

            "Bedroom":"3"

        }

        ]

    },

 "owner2":{

        "id":"L002",

        "property":[ {

            "propertyID":"P003",

            "Property address":"4 st LA",  

            "Rent":"$500",

            "Bedroom":"4"

        }

    ]

},

"owner3":{

    "id":"L003",

    "property":[ {

        "propertyID":"P004",

        "Property address":"20 st LA",  

            "Rent":"$400",

            "Bedroom":"2"

    }

]

}

}

Thank You.

[SudeepGhatakNZ]

See if this helps

Solved: Data Table to JSON object in PAD - Power Platform Community (microsoft.com)

[garry9318]

Hi @SudeepGhatakNZ 

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.

[SudeepGhatakNZ]

@garry9318 ,

You can query your Excel file and then use Select action to form a JSON array.

[grantjenkins]

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'))

[garry9318]

Hi   @grantjenkins 

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

owner1	1	P001	12 st LA	$300	2
owner1	1	P002	14 Louis st LA	$500	2
owner2	2	P003	15 Merry st LA	$550	2
owner2	2	P004	29 John st LA	$1093	2

Output got :

{

  "": {

    "id": "2",

    "property": [

      {

        "PropertyID": "P003",

        "Address": "15 Merry st LA",

        "Market rent": "$550",

        "No of Bedrooms ": "2"

      },

      {

        "PropertyID": "P004",

        "Address": "29 John st LA",

        "Market rent": "$1093",

        "No of Bedrooms ": "2"

      }

    ]

  }

}

Required Output:

{

    "owner1":{

        "id":"L001",

        "property":[ {

            "propertyID":"P001",

            "Property address":"12 st LA",  

            "Rent":"$300",

            "Bedroom":"2"

        },

        {

            "propertyID":"P002",

            "Property address":"3 st LA",  

            "Rent":"$450",

            "Bedroom":"3"

        }

        ]

    },

 "owner2":{

        "id":"L002",

        "property":[ {

            "propertyID":"P003",

            "Property address":"4 st LA",  

            "Rent":"$500",

            "Bedroom":"4"

        }

    ]

},

"owner3":{

    "id":"L003",

    "property":[ {

        "propertyID":"P004",

        "Property address":"20 st LA",  

            "Rent":"$400",

            "Bedroom":"2"

    }

]

}

}

Can you help me on that please.

[grantjenkins]

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:

{
  "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"
      }
    ]
  }
}

[garry9318]

Hi @grantjenkins 

Here's the flow  inputs:

union(body('Select'),body('Select'))

items('Apply_to_each')?['id']

item()?['PropertyID']

item()?['Address']

item()?['Market rent']

item()?['No of Bedrooms']

concat('"', items('Apply_to_each')?['Name'], '":{"id":"', items('Apply_to_each')?['id'], '","property":', body('Select_Properties'), '},')json(variables('output'))

Thank You.

[grantjenkins]

In your first Select can you change the Header from Id to id (lowercase i) to see if that fixes it.

[grantjenkins]

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).

[grantjenkins]

Also, can you check that the input into your Apply to each is using the output from the Compose and not the Select.

[garry9318]

Hi @grantjenkins 

Thank You so much for your help, finally it worked.