Data Table to JSON object in PAD

[LyCo]

Hi!

I have a data table (in Excel) that I need to send via an API call (invoke web action) in Power Automate Desktop.

Is there an easy way to convert this table to a JSON object? Or how is this usually handled in similar situations when data needs to get passed via this action?

Also, I noticed that in Winautomation there were the features of creating a new custom object and adding a new property to a custom object. Has this action been removed in PAD?

Thanks in advance for any input.

[Anonymous · Accepted solution]

Power Automate Desktop is able to convert a custom object's property with datatable/datarow in it straight into JSON.

You can create a custom object with:

%NewVar% = %{{ }}%

Then create a new property and assign your datatable (note that if property does not exist, it creates it for you in the object. That is why there is no command 'add property to custom object').

%NewVar['Table']% = %Table%

Then you can convert your custom object to JSON with the command 'Convert custom object to JSON'. The result will be a list of objects.

[LyCo]

Ok, makes sense! Thanks a lot!

[vamsi_varanasi]

@Anonymous - Thanks for the solution. 

Could you please explain a bit more, I am in need of this solution. I have a two dimensional data table in PAD and have to pass this data table object or JSON to power automate to parse. 

[AgniusBartninka]

@vamsi_varanasi, it is quite straight forward. When you have a table in a variable (e.g. %Table%), you can simply push it into a custom object and then convert it to Json.

Here's a quick sample. I created a simple Excel file with the following table:

Key	Value
One	1
Two	2
Three	3

I then read the file, extracting data so that the first row is a header row. Here's the result from PAD:

I then used the following three actions (you can copy and paste this directly to PAD):

SET NewVar TO {{ }}
SET NewVar['Table'] TO Table
Variables.ConvertCustomObjectToJson CustomObject: NewVar Json=> CustomObjectAsJson

Looks like this in PAD:

What it does is as follows:

1. Create a new empty custom object named %NewVar% (you don't really need to create a new object if you already have one, though)
2. Add a property named 'Table' to the custom object with %Table% as its value*
3. Convert the custom object to Json

* Note: If you already have a property with the same name in the object, it will be overwritten. If you do not, it will be added as a new property.

The custom object (%NewVar%) will look like this:

The 'Table' property inside of it will look like this:

And the Json data after conversion (%CustomObjectAsJson%) will look like this:

{"Table":[{"Key":"One","Value":"1"},{"Key":"Two","Value":"2"},{"Key":"Three","Value":"3"}]}

As you can see, it basically is converted into a list of objects, with each row being a list item, and each value being added with the column names as keys.

This can then easily be parsed by Power Automate.

[vamsi_varanasi]

Thank you very much for your explanation @AgniusBartninka .

My data table is below ( two dimensional table ) which contained 50 records 

I hope the same below syntax should work for above table as well.  Please correct me if I am wrong.

Do you have any suggestion how to delete any specific row from  above data table ?

Thanks,

[AgniusBartninka]

It should definitely work, with the exception that you need to use proper variable names. Mine were just examples.

As for deleting rows from a table - there is no action that would allow doing it. If you want to do that, the only viable option is instead of pushing the entire table into the object, instead create a new separate list, loop through the rows of the table and add the relevant rows to this list, while skipping those that are irrelevant. You can then add the list of rows into the object and convert it to JSON. The end result will be exactly the same, but with less rows (excluding those you skipped in the loop).

[vamsi_varanasi]

Thanks @AgniusBartninka , do we need to create a empty list ( %List[]%) or do we need to create a Empty Data table ( %Table[][]%) to push the specific records ?

[AgniusBartninka]

That is up to you, but since your goal is converting it to JSON, you are perfectly fine with having an empty list and pushing data table rows as list items into the list. This is because as soon as you convert it from a custom object to JSON, you will anyway end up with having a list of objects. Attempting to create a new table and then push values to each column in each row will simply make your flow more complex without bringing any added benefit.

[vamsi_varanasi]

Thank you again @AgniusBartninka .

When I am following the same steps, I am facing the below issue while assigning the list to the custom object ? do you think what could be the issue ?

[AgniusBartninka]

Well, the error is quite self explanatory - the variable does not exist, meaning you have not created the object, before trying to add the property to it.

[vamsi_varanasi]

@AgniusBartninka - Sorry, I had overlooked. able to resolve. I got the below format in Power Automate. 

do you think the below JSON format is correct as the format you have posted in your example was different.

it has Row Error , RowState , etc and these are not from my fields. 

[AgniusBartninka]

Yes, apparently Data Row objects have some additional parameters that are not visible (nor accessible), unless you put it into an object and then convert to JSON. It is perfectly fine to go with this - it looks different than the other format, but it still is a valid JSON string that you can parse in Power Automate.

Alternatively, if you want to avoid having those extra values, you could pick out the explicit values you need, convert it to an object and add the object to the list, instead of the whole data row. For instance, my table has two columns: "Key" and "Value", so, instead of putting the whole data row into a list of data rows, I can put 

{'Key': '%Row['Key']%', 'Value': '%Row['Value']%'}

to the list. This is basically a JSON string already, that contains the column names and the values for each column.

If you have a dynamic data table with an unspecified number of columns, you can also loop through those. Also, if your columns are not named, you can use indexes instead of column names:

{'Key': '%Row[0]%', 'Value': '%Row[1]%'}

If you put this into a Convert JSON to custom object action and then add the output of that action to the list, you will end up with something like this after you convert %NewVar% back to JSON:

{"List":[{"Properties":{"Key":"One","Value":"1"}},{"Properties":{"Key":"Two","Value":"2"}},{"Properties":{"Key":"Three","Value":"3"}}]}

Please note, that the "Properties" part of this is actually a known existing bug in PAD, that is added when you convert a custom object that contains a list of objects to JSON. MS is working on fixing this in the next release. If you want to avoid this, you would need to replace this before passing it as output to PA.

The above is, however, basically a pretty ugly workaround. My suggestion would be passing the entire table the way it was shown earlier to PA and then using PA logic to skip irrelevant lines.

[vamsi_varanasi]

Thank you again @AgniusBartninka .

Let me test this end to end and share you the results. 

[Amit_Chaudhary]

Hi @AgniusBartninka 

I am new to power automate.

Can you suggest me how to extract values from json as per key in PAD?

[AgniusBartninka]

Hello and welcome,

In order to extract values from JSON, you would first need to convert the JSON string to an object in PAD by using the 'Convert JSON to custom object' action.

For instance, here's a simple code that

1. creates a JSON string as {'Key': 'Value'} and stores it in a variable called %JSON%
2. converts the string to a custom object and stores the object into %CustomObject%
3. retrieves the value of the 'Key' element from the object and stores it into %Value%

SET JSON TO $'''{\'Key\': \'Value\'}'''
Variables.ConvertJsonToCustomObject Json: JSON CustomObject=> CustomObject
SET Value TO CustomObject['Key']

 The syntax is simple - %Object['Key']%.

Note: If the key is a variable, you do not need the single quotes. Those are used to pass in a literal value.

Also, the same works for adjusting a value of an object, or adding a new property with a value.

For instance, the following code would change the value of the same 'Key' in the object with 'value2':

SET CustomObject['Key'] TO $'''value2'''

And if the Key you are trying to set does not exist, it will be added. So, the following code would add a new property called "Key2" with a value "Value2" to the object:

SET CustomObject['Key2'] TO $'''Value2'''

[humanstorm2]

Awesome Contents. I just learn something new about PAD today regarding create a custom object to an empty variable.

[takolota]

Thanks @AgniusBartninka,

I was looking for any better ways to pass large tables of data from Power Automate Desktop to cloud flows to SharePoint. This thread seems to explain some of the current state of PAD data-tables to JSON. It would be nice if there was an efficient way to convert large datasets to JSON before sending to cloud flows as that would avoid possible other parsing issues with in-data commas. But if I understood this thread correctly, it sounds like my current set-up, parsing everything from CSV type tables/strings in the cloud flows is more efficient.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Automate-Reports-From-External-Datasourc...

[muhammednihad]

I get errors when converting datatable into JSON. I followed steps explained by @AgniusBartninka

Errors:

Where did I go wrong?🤔

What I need to achieve is: When an update, insert, or delete happens on an SQL database table, need to fetch those data and convert into JSON then pass it into API.

Quick help needed. Thanks❤️

[AgniusBartninka]

Seems to me the syntax in your Set variable action (action #4) is incorrect.

Please make sure you use the following value to set the variable:

%{{ }}%

 This will create a custom object.