Skip to main content

Notifications

Community site session details
Power Automate - Power Automate Desktop
Answered

Adding Variable As A Column To Datatable

Like (0) ShareShare
ReportReport
Posted on 14 Jun 2024 19:57:16 by 77

How do I add a variable as a column to each row of a datatable? Example

 

Datatable contains Names and Ages, in this case all the members of the group are males so I want value of the variable "sex". which is Male to appear on each row of Datatable.

 

Thank you

Before

GenAi_0-1718394886612.png

 

After, should look like this

GenAi_2-1718394991935.png

 

 

 

  • Verified answer
    Deenuji_Loganathan_ Profile Picture
    6,105 Super User 2025 Season 1 on 19 Jun 2024 at 01:15:32
    Re: Adding Variable As A Column To Datatable

    @GenAi 

     

    Please accept both suggestions as solution. Thank you and Happy Automation!!

  • GenAi Profile Picture
    77 on 18 Jun 2024 at 22:09:33
    Re: Adding Variable As A Column To Datatable

    I took @VishnuReddy1997 procedure and used @Deenuji code to add the column. It worked beautifully. Thank you two so much. I don't know which one to accept as the solution since its a joint effort.

  • Verified answer
    GenAi Profile Picture
    77 on 18 Jun 2024 at 21:49:28
    Re: Adding Variable As A Column To Datatable

    I tried your suggestion an got an error message (Please see below). Also, I was wondering with your solution, do I need to state all the variable inputs? There are lots of rows, almost impossible to state all of them.   What do you suggest? Sorry I am a beginner and making it harder to get help. Thank you

    Variables.CreateNewDatatable InputTable: { ^['Name', 'Age'], [$'''Sam''', 10], [$'''Peter''', 22], [$'''Mike''', 30], [$'''Ray''', 40] }

     

     

    GenAi_0-1718747299421.png

     

  • Verified answer
    Deenuji_Loganathan_ Profile Picture
    6,105 Super User 2025 Season 1 on 18 Jun 2024 at 03:12:24
    Re: Adding Variable As A Column To Datatable

    @GenAi 

     

    Lets make it simple. I go with your iterated example:

    Deenuji_1-1718679757517.png

     

    Then I pass this datatable to .Net script where it will do all the magics.

     

    After executing .Net script:

    Deenuji_2-1718679817463.png

    Overall flow screenshot:

    Deenuji_4-1718679929934.png

     

     

    To integrate this logic into your workflow:

    1. Drag and drop the .Net script into your workspace.

    2. Click the Edit button on the script parameter and create a .Net parameter as shown in the screenshot below.

    3. Insert the C# code as displayed below and modify it if you require a different default value (for instance, "Male" is set as the default here).

     

    The function will accept your datatable as input, insert an "Account" column with the default value "Male," and then return the updated datatable.

     

    Deenuji_3-1718679882527.png

     

     

    Full workflow code (To understand how it works, simply copy and paste the full workflow code into your PAD flow):

    Variables.CreateNewDatatable InputTable: { ^['Name', 'Age'], [$'''Sam''', 10], [$'''Peter''', 22], [$'''Mike''', 30], [$'''Ray''', 40] } DataTable=> Header_048
    Scripting.RunDotNetScript Imports: $'''System.Data''' Language: System.DotNetActionLanguageType.CSharp Script: $''' // Check if the Gender column already exists
     if (!dt.Columns.Contains(\"Gender\"))
     {
     // Add the Gender column with default value \"Male\"
     DataColumn genderColumn = dt.Columns.Add(\"Gender\", typeof(string));
     genderColumn.DefaultValue = \"Male\";
    
     // Set default value for existing rows (if any)
     foreach (DataRow row in dt.Rows)
     {
     row[\"Gender\"] = genderColumn.DefaultValue;
     }
     }''' @'name:dt': Header_048 @'type:dt': $'''Datatable''' @'direction:dt': $'''InOut''' @dt=> Header_048

     

     


    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 🚀

  • GenAi Profile Picture
    77 on 17 Jun 2024 at 15:51:49
    Re: Adding Variable As A Column To Datatable

    I see your logic and love it but realized that I asked the wrong question.  My datatable was extracted from an html table and doesn't contain a column header "Account".  I have updated the flow based on your logic but not sure how I can add the column header " Account" to the table. Please see below my exact flow.  The single datatable "Header_048" is what I want to appear in the column " Account" for each row. How do I get this done? Thank you

     

    GenAi_0-1718639086961.pngGenAi_1-1718639456557.png

     

  • Verified answer
    VishnuReddy1997 Profile Picture
    2,324 Super User 2025 Season 1 on 15 Jun 2024 at 04:39:29
    Re: Adding Variable As A Column To Datatable

    Hi @GenAi ,

     

    Please find the below.If you want you can set that Sex into variable and call that in Update data table item.

    VishnuReddy1997_0-1718426286119.png

     

    Code:

    Variables.CreateNewDatatable InputTable: { ^['Name', 'Age', 'Gender'], [$'''Sam''', 10, $''''''], [$'''Peter''', 20, $''''''], [$'''Mike''', 30, $''''''], [$'''Ray''', 40, $''''''] } DataTable=> DataTable
    SET RowValue TO 0
    LOOP FOREACH CurrentItem IN DataTable
     Variables.ModifyDataTableItem DataTable: DataTable ColumnNameOrIndex: $'''Gender''' RowIndex: RowValue Value: $'''Male'''
     Variables.IncreaseVariable Value: RowValue IncrementValue: 1
    END

     

    (Note:- if you got your solution you can mark as solution and gives kudos)


    Thanks & Regards

    Vishnu Reddy

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,079 Most Valuable Professional

Leaderboard