cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gareth-
Advocate II
Advocate II

List Rows present in a table - how do I programatically specify the table?

Hi Microsoft,

It does not seem possible to use a variable or an expression to specify the name of the table in the 'List rows present in a table' step in PowerAutomate.  See picture - red dot - 

Gareth-_0-1605258968891.png

this can only be hard-coded at flow creation.

I believe that this is a serious limitation that significantly reduces the value of (a) Office Script and (b) Power automate integration into office script.

 

The general use case that is broken is like this (one example of many):

 

(1) New Data is generated in an excel file -->    (2) an office script program is triggered by Flow to transform the data --> (3) the data is consolidated into another file, which is used as a power BI data source --> (4) the Power BI report is refreshed automatically.

 

The data in step (1) is periodic i.e. hourly, weekly, monthly etc. [it does not matter] 

 

The problem comes in step (3) as you cannot read the data from the Excel table from the file created in step (1) as the file has been created after the flow was programmed. You can use other flow steps to get the file properties (ie filename, the names of the tables in the file), but you cannot programmatically put the table name into the 'List rows present in a table step'

 

Am i missing something? (that would be great - please let me know!) otherwise this is like having a jigsaw with a piece missing.

 

any guidance appreciated.

 

 

 

 

 

 

 

 

 

 

 

 

 

2 REPLIES 2
danwilso
Microsoft
Microsoft

Hey Gareth!

 

Great callout, thanks for your feedback—you are correct, "List rows present in a table" isn't really usable for more complex scenarios like this; it's really meant to read from a specific table in a specific workbook which doesn't change.

 

It would be hard to enable this action for dynamic table names; since the action loads the table's headers for use later in the flow with the dynamic content pane, it tries to read the headers from the selected table—which doesn't really work if you also want to work with tables that don't exist yet.

 

Since you're already using Office Scripts, I'd accomplish this by creating an Office Script to mimic the functionality of "List rows present in a table," but one which accepts a tableName parameter.

 

Here's my attempt below—should work for you with some minor adjustments:

 

/**
 * Lists rows present in a specific table named tableName
 * 
 * This script helps to overcome some shortcomings with Power Automate's Excel Online (Business) connector "List rows present in a table" action which does not allow for dynamically defined table names.
 */
function main(workbook: ExcelScript.Workbook, tableName: string = "Table1") {
    // First, get the correct table according to the parameter received from Power Automate - or some default if testing the script in Excel for the Web
    const table = workbook.getTable(tableName);

    // Get the range of only the body of the table, not the header
    const tableRange = table.getRangeBetweenHeaderAndTotal();

    // Next, get the cell values from the table range - tableRange actually contains Excel objects, and we only want the values.
    const tableValues = tableRange.getValues();

    /**
     * Convert the array of table row / cell values to be an array of cell value objects where each cell value has its corresponding header, e.g.:
     * 
     * { header: value }
     * 
     * This will allow us to select specific values from rows when using the script in Power Automate, just as the "List rows present in a specific table" action does.
     * 
     * You don't need this part if you're not using the "Dynamic content" pane in the flow designer.
     */

    // We must specify the table headers or the rows won't show up nicely in Power Automate. We must also specify the headers below in the TypeScript type (... as { ... }).
    const tableHeaders = ["A", "B", "C"];

    const result = tableValues.map((rowValues) => {
      return rowValues.reduce((row, cellValue, i) => {
        const header = tableHeaders[i];
        row[header] = cellValue;

        return row;
      }, {} as {
        A: string;
        B: string;
        C: string;
      });
    });

    console.log(result);

    return result;
}

 

You'll have to adjust the tableHeaders variable to match your column headers (same for the "...row as { A:... }" near the end). This should accomplish more or less what you're looking for, assuming your tables all have the same headers.

Gareth-
Advocate II
Advocate II

Hi danwilso (Dan?),

 

Many thanks for this.  I will give it a go.  thanks for taking the time to reply.

 

regards

Gareth 

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Users online (1,474)