How to Add Data to Data Table

[chad15]

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! 

[yasunm02 · Accepted solution]

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]

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]

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

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

[ryule]

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

[damien-mick]

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

[ryule]

No, unfortunately I haven't 😞

[Florent_CHAUVIN]

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]

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

[jhaskamp]

Worked for me, liking the formatting - thanks!