cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HellitonWoo
Regular Visitor

Create different types of variables

Hi fellows,

 

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

  • data table
  • JSON
  • dictionary

 

Thanks!

Hellitonwoo

15 REPLIES 15
adijain
Microsoft
Microsoft

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. 

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

HellitonWoo_0-1602619521042.png

 

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

HellitonWoo_1-1602619577644.png

 

it would be great if you can further assist here

 

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.

Rikke
Frequent Visitor

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.

Rikke
Frequent Visitor

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

Anonymous
Not applicable

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

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

 

shindomo_0-1631095000744.png

 

@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
Not applicable

Hello @soumya_12,

 

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.

shindomo_0-1631598983501.png

 

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.

 

shindomo_1-1631599192658.png

 

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.

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 fileoverwriting existing content in a file

Anonymous
Not applicable

Hello @soumya_12,

 

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

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

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.

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. 

miennguyen
Regular Visitor

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
Microsoft
Microsoft

@miennguyen @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.  

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022- Season 2 has kicked off!

Top Solution Authors
Top Kudoed Authors
Users online (3,084)