List rows present in a table in Excel Connector(Power Automate Cloud flows)
This action retrieves all rows of an Excel table.
How to use
The target Excel file must be stored in OneDrive or SharePoint.
The table to be retrieved must be a table.
In this example, we will prepare a table with the items "Code", "Name", "Price", and "Qty".
Select the "Excel Online (Business)" connector when adding steps to the flow.
Select the "List rows present in a table" from the "Action" menu.
Set Parameters.
Parameters
Location
A list of OneDrive and SharePoints that can be connected will be displayed.
From among them, select the location where the target file is located.
Document Library
For OneDrive, select "OneDrive"; for SharePoint, select "Document".
File
Select a file.
It looks like a file path, but it is only a display and the content is an ID.
If you delete the specified file and then create a file with the same name,
it will be treated as a different file. (Reassignment is required.)
Table
A list of tables that exist in "File" will be displayed.
Select the table of interest from the list.
Filter Query
Specifies the condition for retrieval. With the default value of blank, all rows are retrieved.
For example, in this sample, if "Name eq 'Orange'" is specified, only the fourth row of data will be retrieved.
However, this item is difficult to specify, and there are many restrictions such as not being able to use comparisons such as more than or less than.
Therefore, it would be more efficient to retrieve all rows without specifying a "Filter Query" and filter the data using a Condition.
Order By
Specify one column name to sort by.
If the default value is left blank, the data will be obtained in the Excel sort order.
If "Name acs" is specified in this sample, the list will be sorted by "name" in ascending order. (acs can be omitted).
If "Price desc" is specified, the list will be sorted by "price" in descending order.
Top Count
The maximum number of rows to retrieve. With the default value of blank, all rows are retrieved.
This is useful when you want to limit the number of rows to be retrieved for reasons such as processing time.
Skip Count
The specified number of rows are skipped.
With the default value of blank, the first line is retrieved. (excluding header lines).
Select Query
Specify the column names to be retrieved, separated by commas.
If "Name,Code" is specified in this sample, "Price" can be specified in the dynamic content, but the content will be blank.
If no column is specified, all columns are retrieved.
DateTime Format
Select the date retrieval format.
- Serial Number:Number format.
- ISO 8601:Date format.
In both cases, the variable is received as a "String" type.
Dynamic content
To use rows retrieved from Excel, "Apply to each" is required.
Specify a dynamic content "value" in "Select an output from previous steps".
This will cause the action placed in "Apply to each" to loop through the number of rows retrieved.
In "Apply to each", there is dynamic content for item names, which can be used to retrieve data.
---