Create different types of variables

[HellitonWoo]

Hi fellows,

Is it possible to create the following types of variables or as inputs?

• data table
• JSON
• dictionary

Thanks!

Hellitonwoo

[adijain]

Hello @HellitonWoo ,

Power Automate Desktop supports creation of DataTable and JSON variables but not dictionaries. Although, a RowTable can be accessed from a DataTable which has similar functionality as of a dictionary where column names can be used as dict keys. 

Hope it makes sense. 

[HellitonWoo]

@adijain 

Could you further explain where can I create a "Datatable" or "JSON" type of variable?

I don't think I can change the type when creating an "Input".

All inputs are accepted as "String". Thus it takes quite a lot of work to "deserialize" a JSON (from Power Automate web) and re-packaging it into a "list", then feed it to the "UI flow". And to use that Input, I have to split it again, so forth...

Also, when creating a "Variable", there's no option to choose the type

it would be great if you can further assist here

[adijain]

Hello @HellitonWoo,

Apologies for the confusion. But, input/output variables in PAD currently only supports creating string data type to get/send data from Power Automate portal. Other data types will be added in the future. 

Additionally, for variables within a PAD flow, datatables are created as an output of several PAD Actions, like "Read from Excel", "Read from CSV", "Extract data from web page", "Extract data from window". And a data row can accessed by targeting any one of its row (e.g %DataTable[0]% is a data row) or you can iterate a datatable using "For each" loop. Creating the datatables from scratch with just one action is not yet available in PAD preview, but part of our roadmap.

[Anonymous]

To create a datatable you can use the set variable action and programming array notation: %{['Product1', '10 USD'], ['Product2', '20 USD']}%

You similarly create a custom object by using the following notation:%{ 'Property1': 'Value1', 'Property2': 'Value2', 'Property3': 'Value2' }%, you can also create lists this way, and even put lists as property values. A custom object can be converted to at json-string with the convert to json action.

[Anonymous]

This is not true. You can create datatables from scratch using the array programming notation, as described on this site.

[Anonymous]

You can also create a Datatable which has specific column name:

%{ ^['Name', 'Phone', 'State'], ['Alice', '1234', 'AA'], ['Bob', '2345', 'BB'], ['Cindy', '3456', 'CC'] }%

[Anonymous]

@Anonymous  I have  a list with me , after I performed actions like 'Remove duplicate items in the list'. I have to change the list values(generic value type) to data table (with data rows), the data coming from this action 'remove duplicate items in the list'. As i need to write the data back to a excel file. And as you mentioned above %{ ^['Name', 'Phone', 'State'], ['Alice', '1234', 'AA'], ['Bob', '2345', 'BB'], ['Cindy', '3456', 'CC'] }%. 

My list contains dynamic values , as every time it will be changing. not specific one's . So kindly suggest what format to use for dynamic values , not fixed values.

Kindly look into this .Thanks in advance.

[Anonymous]

Hello @Anonymous,

As far as I know, you do not need to convert a List to a Datatable to write data to an Excel worksheet.

Just use variable %List% for 'Value to write' field of "Wite to Excel worksheet" action.

If you dare to convert from List to Datatable, please refer below sample flow.

POINT: You can join a List to a Datatable only if the number of columns in the Datatable and the List is the same.

Variables.CreateNewList List=> List
Variables.AddItemToList Item: $'''Alice''' List: List NewList=> List
Variables.AddItemToList Item: $'''Bob''' List: List NewList=> List
Variables.AddItemToList Item: $'''Cindy''' List: List NewList=> List
Variables.AddItemToList Item: $'''Bob''' List: List NewList=> List
Variables.AddItemToList Item: $'''Bob''' List: List NewList=> List
Variables.RemoveDuplicateItemsFromList List: List IgnoreCase: False NewList=> List
SET Count TO List.Count
SET Datatable TO { ^['FirstName'] }
LOOP LoopIndex FROM 0 TO Count - 1 STEP 1
    Variables.CreateNewList List=> ListForCurrentItem
    Variables.AddItemToList Item: List[LoopIndex] List: ListForCurrentItem NewList=> ListForCurrentItem
    SET Datatable TO Datatable + ListForCurrentItem
END
Excel.Launch Visible: True LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.WriteCell Instance: ExcelInstance Value: List Column: 1 Row: 1
Excel.WriteCell Instance: ExcelInstance Value: Datatable Column: 2 Row: 1

Thank you.

[Anonymous]

Thank you @Anonymous for your response. The main aim is to remove the repeated items( overwrite the present data with new data) in my excel file.  As I have a database filtering all the duplicates with (primary key as ID column), but as I have only 1 database storing all the data & have to apply some filters on the data. So this flow is triggered by cloud flow (when an file is created) & then stores in DB & then applying filters.

The main issue here is , as filter( The query to DB) applies on all the data in DB , I get the previous data along with new data . So the data in excel is written twice along with new one's. So it has to over write the old data , with new one's.

Attaching screenshot for reference(overwriting existing content), as we have for a text file , I couldn't find out for excel actions, so trying the above workaround you mentioned.

Kindly look into the same. Expecting quick response please.

overwriting existing content in a file

[Anonymous]

Hello @Anonymous,

I am not sure if I understand your explanation correctly...

But if your main problem is the OLD data which has been retrieved everytime the flow is executed, then you should write to Excel the whole data from begin to end, I think. For example, create a new Excel file which contains every data, and replace the old Excel file with the new Excel file, everytime the flow execute. It's simpler and easier to implement.

I think your problem is not related to the Variables discussed here in this topic. So, please create a new thread and post your own question there. 😉

[Anonymous]

The scenario is little different. yes will drop in another thread.

[Anonymous]

Hi, 

Is there an update on input/output variable data type? I can't create an input variable with any data type different from String.

Thank you!

Mien.

[MichaelAnnis]

According to this:

https://docs.microsoft.com/en-us/learn/modules/input-parameters/3-define-input-variable

there should be a drop down on an input variable type. 

[Anonymous]

Thank you Michael, 

I don't have a dropdown box at the Data Type at all, just a Textbox. I ended it store data (as a datatable) in a file, the input variable is the file path (String).

Thank you,

Mien.

[adijain]

@Anonymous @MichaelAnnis Currently input variables in Power Automate for desktop only supports String as a DataType. We do have plans to include more in the roadmap for FY22.