cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chad15
New Member

How to Add Data to Data Table

How would I go about adding a row or column to a data table after it is created. For example, I know how to create a data table by using set variable to something like:

 

%{['Test', ['1']}%

 

 But then how would I either add an additional row of data or add/update an existing column? If I do set variable again it erases what was currently there and if I do add to list, it creates a table inside a table it appears. Any tips would be greatly appreciated! 

1 ACCEPTED SOLUTION

Accepted Solutions
yasunm02
Solution Sage
Solution Sage

See Delete a row/column from a datatable - Power Automate | Microsoft Docs

According to PAD documentation, there's no a direct way to delete rows/columns from datatables, Instead, it offers a workaround through Excel. As the opposite is also true (there's no a direct way to add rows/columns), I think adding a row to a datatable goes in the same way:
1. Launch Excel with a blank document
2. Write DataTable to Excel
3. Write to Excel worksheet - write the values of neew row
4. Read from Excel worksheet - retrieve all available values from worksheet

View solution in original post

8 REPLIES 8
yasunm02
Solution Sage
Solution Sage

See Delete a row/column from a datatable - Power Automate | Microsoft Docs

According to PAD documentation, there's no a direct way to delete rows/columns from datatables, Instead, it offers a workaround through Excel. As the opposite is also true (there's no a direct way to add rows/columns), I think adding a row to a datatable goes in the same way:
1. Launch Excel with a blank document
2. Write DataTable to Excel
3. Write to Excel worksheet - write the values of neew row
4. Read from Excel worksheet - retrieve all available values from worksheet

MichaelAnnis
Super User
Super User

I have never worked with a Data Table inside of Power Automate, so my only support to give you is a work around:

 

1) Write the existing Data Table to Excel

2) Make the changes you want to make

3) Pull the completed data table back in

 

Good luck

RuslanPopescu
New Member

You can add a row in a datatable variable as following:

Set Variable: DtTable = %DtTable + ['textColumn1','textColumn2']%

@RuslanPopescu have you had any success adding a variable value to a new row in a datatable? For instance:

 

Set Variable: DtTable = %DtTable + ['%CurrentItem.Name%', 'textColumn1','textColumn2']%

 

It just writes it as text, and if I remove the apostrophe, it just gives me a syntax error.

@ryule I have been trying to figure out this same problem. The only solution I can think of is to turn each variable into a constant and populate the datatable with these constants (which is a messy and painful solution).

Have you had any luck with this problem?

No, unfortunately I haven't 😞

Hello,

@ryule@damien-mick in fact i discover today that you can use variable when you define or update datatable and it works for headers too !

I found the solution in this post : https://www.flowjoe.io/2022/04/12/add-variables-to-a-data-table-on-power-automate-desktop

The tips is to not add percent (%) and single quote (') before and after your datatable variable name.

 

For example i have done a web extract with a first datatable with table headers and second datatable with table data and i want to join these two datatables.

 

So i define a new datatable variablewith 9 headers issued from second column of first datatable named DataReportHeader.

So below the  value of my new datable variable 'Report':

 

 

%{^[ReportHeader[0][1],ReportHeader[1][1],ReportHeader[2][1],ReportHeader[3][1],ReportHeader[4][1],ReportHeader[5][1],ReportHeader[6][1],ReportHeader[7][1],ReportHeader[8][1]]}%

Code to paste in PAD:

SET Report TO { ^[ReportHeader[0][1], ReportHeader[1][1], ReportHeader[2][1], ReportHeader[3][1], ReportHeader[4][1], ReportHeader[5][1], ReportHeader[6][1], ReportHeader[7][1], ReportHeader[8][1]] }

 

 

And after i can update this new datatable by adding data from second datatable named DataReport, here we iterate with foreach action and we update variable on each loop :

 

 

If you want to take specifics columns values, below the variable value :

%Report + [CurrentItem[1], CurrentItem[2], CurrentItem[3], CurrentItem[4], CurrentItem[5], CurrentItem[6], CurrentItem[7], CurrentItem[8]]%

Code to past in PAD with foreach loop :

LOOP FOREACH CurrentItem IN ReportData
SET Report TO Report + [CurrentItem[1], CurrentItem[2], CurrentItem[3], CurrentItem[4], CurrentItem[5], CurrentItem[6], CurrentItem[7], CurrentItem[8]]
END

Or if you want the complete row with all columns values :

%Report + CurrentItem%

Code to past in PAD with foreach loop :

LOOP FOREACH CurrentItem IN ReportData
SET Report TO Report + CurrentItem
END

 

 

Enjoy !

 

amanwadhwa990
New Member

Use %vData + [rowd, rowdd]% where rowd and rowdd are variables.

Helpful resources

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

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

AI Builder AMA June 7th carousel (up on May 25th, take down June 8th) (1).png

'Ask Microsoft Anything' about AI Builder!

The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.

Users online (1,934)