Answered

Passing Datatable(s) from PAD to cloud Error

(0) Share

Report

Posted on by jonathanoh591

Hi there,

I created 3 output variables in PAD, all of type datatable. These are populated via powershell scripts. As seen, these populated as expected:

However, when I return to my cloud flow, they are not populated:

Looking online, it seems like I need to convert my datatables -> CustomObject -> JSON, but I am unsure on how to do this and unsure whether this is best practice. Any help with this would be highly appreciated, thanks

Categories:

Power Automate Desktop

All responses (10)

Answers (1)

Sort by

CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@jonathanoh591

Please follow the below approach for your use case:

Convert DataTable to JSON:
A new custom object, JsonOutput, is created and initialized as an empty object (SET JsonOutput TO {{ }}).
The DataTable is assigned to the 'Table' property of the JsonOutput object (SET JsonOutput['Table'] TO DataTable).
Finally, the Variables.ConvertCustomObjectToJson command is used to convert the JsonOutput object (containing the DataTable) into a JSON string. The resulting JSON string is stored in the DataTableAsJson variable.

Flow Screenshot:

Code

# Initialize a new DataTable SET DataTable TO { ^['ID', 'Name'] } # Add rows to the DataTable SET Row1 TO [1, 'John'] SET Row2 TO [2, 'Alice'] SET Row3 TO [3, 'Bob'] # Add the rows to the DataTable Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Row1 Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Row2 Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Row3 # Convert the DataTable to JSON SET JsonOutput TO {{ }} SET JsonOutput['Table'] TO DataTable Variables.ConvertCustomObjectToJson CustomObject: JsonOutput Json=> DataTableAsJson # Output the JSON string

How to copy/paste the above code into your PAD?

We can also use .Net or powershell scripts to convert the datatable into json values. Hope it helps.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
jonathanoh591 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi there @Deenuji

Thank you for your response; I was wondering if the 'output the JSON string' is done by assigning it to the output variable (in my case say the output datatable variable, 'OverallTrafficData')?

Do I need to change the output variable typing to Text and then converting my data back once it is received in my Cloud flow?

Many thanks,
Jonathan
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@jonathanoh591

Indeed, your interpretation is accurate. You can allocate the output of your JSON conversion to your designated output variable (the one you're passing into the cloud flow) as follows:

As for the output variable type, even though we've converted it to JSON, Power Automate Desktop treats it as text since it doesn't differentiate data types for JSON. Once you receive this table as text in the cloud flow, you can then convert it into a JSON data type for further parsing. Hope this clarifies things.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
jonathanoh591 on at

Like
a
(0)

Report
Copy link

Link copied!

@Deenuji

this did not work
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@jonathanoh591

Could you please elaborate your issue? If possible pls share screenshot of your code and error message.
jonathanoh591 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Deenuji

Apologies, I forgot to add the images- unfortunately what you proposed has not worked.
Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

You do not actually need to convert your output variables to JSON, if their type is a data table. You should provide them in the same format as the type you set to it. So, if it's a data table, it should be passed in as an actual table.

What seems to be the problem, however, is that you may actually be setting the wrong variable. Are you sure that you have created actual output variables here:

When you have an input/output variable, it does not normally show up under "Flow variables", and it only shows up under "Input / output variables". But your last screenshot shows it under Flow variables. You may have set your variable up incorrectly. Make sure that you use the same name in both of the variable name fields:

When you do that, you will be prompted about the variable with such a name already existing in the Flow variables. You should merge them to make it work.
CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @jonathanoh591

@Agnius ' statement is correct. There's no need to convert the datatable when passing it into cloud flows. I tested it myself, and even when we pass the output variable as a datatable, the cloud flow receives it as JSON values only.

But still you want to convert your datatable as JSON still above suggested solution is valid.

My flow:

While creating your output variable please make sure to mention that as datatable:

Cloud result:

Try the below code[Create new flow in your PAD and copy/paste the below code into your flow]:

# Initialize a new DataTable SET DataTable TO { ^['ID', 'Name'] } # Add rows to the DataTable SET Row1 TO [1, 'John'] SET Row2 TO [2, 'Alice'] SET Row3 TO [3, 'Bob'] # Add the rows to the DataTable Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Row1 Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Row2 Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: Row3 SET OverallTraffic TO DataTable

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀
jonathanoh591 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Angus,

thank you for your help- I was saving my excel data to a flow variable of type data table before then assigning it to the output variable. I have removed this intermediate step and I gained the expected result.

The external name does not need to match the variable name; this is just for how Cloud flows (and potentially other external resources) identify this variable.
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, I am aware they do not need to match, but I was thinking that perhaps you thought your variable was named that when it really wasn't, because the name seemed to be correct in the cloud flow.

Great that you found the issue.