Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Announcing Category Subscriptions!
News and Announcements icon
Power Platform Community / Forums / Power Automate - Building Flows / action "Row Update" in...
Power Automate - Building Flows
Answered

action "Row Update" in the PAD

(0) ShareShare
ReportReport
Posted on by CU31120406-0 2
I want to update the data in the Excel table with a PAD.
There are many tables in Excel.
Obtain the table name in "Table Acquisition" and perform "Row Acquisition".
Next, you want to enter the content you want to update in the "Custom Form" and overwrite the value to the Excel table in "Row Update".
However, if you specify a table name in "Row Update", the dynamic item name will not be displayed.
I looked for it on the Internet, but the current action does not have "specify item properties", so I can't even specify JSON.
I want to know how to update data.
Connector: Excel Online (Business)
File storage destination: OneDrive
File extension: xlsx
  • CU31120406-0 Profile Picture
    CU31120406-0 2 on at
    PADでのアクション「行更新」
    Thank you for the advice.
    Is [3. Update row data (Update a Row)] as a [Custom form] action?
    Or do I need to set up two [Update a Row] actions?

    I followed the advice and created the flow.
    image002.png
    * The [Custom Form] action is not listed in the image, but it has been created without any problems up to the point where the value is displayed in [Custom Form].

    However, I don't know how to overwrite the data updated in the custom form with values in Excel.
    The [Update a Row] action is set as follows, but the fields where you can specify [Key Column] and [Key Value] are not displayed.
    The [table name] is a variable obtained by [Get Table(行の取得)] action.
    [key column(キー列)] is a fixed value. You have specified the column name in Excel.
    [key value(キー値)] is today's date, so we are specifying a variable.
    Even if you set the Row ID obtained by the [List rows in the table] action in the key column, the phenomenon does not change.
    image001.png
    In Excel, [Key Column] and [Key Value] exist.
     image004.png
    However, Power Automate DESKTOP, the [Day of the Week], [Start Time], and [End Time] are not displayed in [Update a Row] action.
     
  • Verified answer
    VASANTH KUMAR BALMADI Profile Picture
    VASANTH KUMAR BALMADI 218 on at
    action "Row Update" in the PAD

    To update data in an Excel table using Power Automate Desktop (PAD) with the Excel Online (Business) connector, here's a step-by-step guide to help you achieve your goal. I'll outline the process, including obtaining the table name and updating rows with the data you want.

    Steps Overview:

    1. Obtain the table name in the "Table Acquisition" step.
    2. Acquire rows using the table name.
    3. Update row data based on user input from the "Custom Form".
    4. Overwrite values in the Excel table.

    Detailed Steps:

    1. Obtain Table Name (Table Acquisition):

      • To dynamically fetch the table names in an Excel file stored in OneDrive, use the "Get Tables" action from the Excel Online (Business) connector.
        • This will return all the table names in your Excel workbook.
        • You can store the table names in a variable, such as TableNames.
      Get Tables
- File: [Your OneDrive Excel File]
- Store output in: TableNames (List of Tables)

    2. Acquire Rows (Row Acquisition):

      • Once you have the table name, you can use the "List Rows Present in a Table" action from the Excel Online (Business) connector to get the data from the specific table.
        • Provide the Table Name dynamically based on the output from the "Get Tables" action.
      List Rows Present in a Table
- File: [Your OneDrive Excel File]
- Table: [Dynamic Table Name (from TableNames)]
- Store output in: ExcelRows

      This will give you all the rows of the specified table. If you want to filter rows, you can use the Filter Query field to specify conditions like ColumnName eq 'Value'.

    3. Update Row Data (Row Update):

      • Now that you have the rows, you can update the specific row based on the user input. The "Update a Row" action is used to update a specific row in the Excel table.
        • You need to specify the row you want to update by providing the Row ID from the "List Rows Present in a Table" action.
        • The dynamic item names in this case refer to the column names of the table, so you’ll have to know the column names ahead of time.
      Update a Row
- File: [Your OneDrive Excel File]
- Table: [Dynamic Table Name (from TableNames)]
- Row ID: [Row ID from ExcelRows]
- ColumnName1: [Value from Custom Form]
- ColumnName2: [Value from Custom Form]
      • The dynamic content for the column names will be automatically populated once you select the row. For updating, the fields that are available are the column names in the table, not JSON data.

    4. Overwriting Values:

      • In the "Row Update" action, you provide values to be updated in specific columns based on the user input.
        • For example, if the user provides a new value for "Product Name", you would update the ProductName column with the new value from the Custom Form.
      Update a Row
- File: [Your OneDrive Excel File]
- Table: [Dynamic Table Name]
- Row ID: [Row ID from List Rows]
- ProductName: [New Product Name]
- Quantity: [New Quantity]

    5. Note on JSON:

      • You mentioned JSON and specifying item properties. Power Automate Desktop doesn't work with raw JSON in Excel updates; you specify the data directly via column names.
      • If you have a dynamic table, you should ensure that the table structure is consistent (same column names) so the dynamic content for the column names is available for use.

    Troubleshooting:

    • Dynamic Item Names Not Showing: This typically occurs if the table columns have not been recognized correctly or if there are no rows returned from the "List Rows" action. Ensure the table has rows and that the columns are properly formatted in Excel.
    • Permissions: Ensure that your account has sufficient permissions to update the Excel file in OneDrive, especially if you’re working in a business or shared environment.
    • File Locks: Excel Online can lock the file if it's being edited, so ensure the file is not open in Excel while the flow is running.

    Summary:

    • Use the "Get Tables" action to dynamically fetch table names.
    • Use "List Rows Present in a Table" to fetch the data for the specified table.
    • Use "Update a Row" to update specific columns in the table with values from the Custom Form.

    By following these steps, you'll be able to dynamically update the Excel tables using Power Automate Desktop (PAD) and the Excel Online (Business) connector.

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

News and Announcements

Welcome to the Power Platform Communities
Announcing Category Subscriptions!

Quick Links

Kickstarter Events…

Register for Microsoft Kickstarter Events…

Tuesday Tip #12 Start your Super User…

Welcome to a brand new series, Tuesday Tips…

Tuesday Tip #13 Writing Effective Answers…

Welcome to a brand new series, Tuesday Tips…

more Community News

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,858

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,505

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans