Do you need to sort your Excel table with Power Automate?

Sorting data can help you quickly find trends, patterns, or outliers in your data. By sorting data in ascending or descending order, you can easily identify the highest or lowest values within a range, or even sort by multiple columns to view results by category.

This might also be a crucial step in Power Automate as you need your flow to process the data and perform actions in a specific order.

This article will explore various techniques for sorting Excel tables with Power Automate.

Sort Excel Data with List Rows Present in a Table Action OrderBy Query

Using an OrderBy Odata query will be the most common way to sort your Excel data.

You can use the List rows present in a table action to retrieve your Excel table data and get it returned in a specified order.

For example, suppose you have the above Excel table to sort named Cars, with the columns ID, Make, Model, and Year.

Here’s how to use the List rows present in a table action with an OrderBy query.

1. Add the List rows present in a table action to your flow.
2. Select the Location of the SharePoint site where the Excel file is saved.
3. Select the Document Library that contains your Excel file.
4. Select the File using the file picker menu.
5. Select the Table in your file that you want to sort.
6. Click on the Show advanced options link at the bottom of the action to reveal more options.

This will reveal the Order By option that will allow you to enter an ODATA orderBy query to return your results in a specific order.

Sort in Ascending Order

YourColumnName asc

To sort Excel data in ascending order, you can use the above Odata query format in the Order By input of the List rows present in a table action in Power Automate.

Here YourColumnName is the column heading of the column you want to sort followed by the asc keyword to sort in ascending order.

Make asc

For example, the above query will sort the Make column in ascending order.

⚠️ Warning: The Order By query can not handle spaces within a column heading. You will need to remove any space characters from your column heading name before referencing it in the Order By query.

Sort in Descending Order

YourColumnName desc

To sort Excel data in descending order, you can use the above Odata query format in the Order By input of the List rows present in a table action.

Here YourColumnName is the column heading you want to sort followed by the desc keyword to sort in descending order.

Make desc

For example, the above query will sort the Make column in descending order.

Sort by Multiple Columns

YourColumnName1 asc, YourColumnName2 desc

Unfortunately, you can’t use the Order By queries to sort by more than one column. If you try a query similar to the above, you will get an error stating that “only single expression is supported“.

Sort Excel Data with Office Scripts

This section will discuss how to sort data in an Excel table using Power Automate and Office Scripts.

While the previous Odata method did not alter the data in the Excel file, this method will. It will sort the Excel table within the Excel file.

You’ll first need to create an Office Script and then you can run it from your flow.

Here’s how you can create an Office Script.

1. Open Excel and go to the Automate tab.
2. Click on the New Script option. This will open the Code Editor where you can add your code.
3. Add the script to the Code Editor. See the next sections for sorting scripts you can use.
4. Click on the script name to rename and save it to a SharePoint folder.

When you save the script to a SharePoint folder, you will then be able to run it with the Run script from SharePoint library action in your flow.

1. Add the Run script from SharePoint library action into your flow.
2. Select the Workbook Location of the SharePoint site that contains the Excel with a table to sort.
3. Select the Workbook Library that contains your Excel file.
4. Choose the Excel file in the Workbook file picker.
5. Select the Script Location where you saved your sorting script.
6. Select the Script Library with your script.
7. Choose the script file with the Script file picker.

This will run the script from your flow!

📒 Read More: You can read more about how to create and run an Office Script in this article about how to update a single cell in Excel with Power Automate.

Sort in Ascending Order

function main(workbook: ExcelScript.Workbook) {
	let myTable = workbook.getTable("YourTableName");
	// Sort on table with column index 1
	myTable.getSort().apply([{key: 1, ascending: true}]);
}

The above script will sort an Excel table named YourTableName on the second column in asceding order.

The script uses a zero-based column index number, so the 1st column has an index 0, the 2nd column has an index 1, etc…

Replace YourTableName in the code with the name of the table you want to sort and key: 1 with the correct index of the column you want to sort by.

Sort in Descending Order

function main(workbook: ExcelScript.Workbook) {
	let myTable = workbook.getTable("YourTableName");
	// Sort on table with column index 1
	myTable.getSort().apply([{key: 1, ascending: fasle}]);
}

You can change the ascending: true keyword to ascending: fasle as the above script and it will sort the Excel table named YourTableName in descending order.

Sort by Multiple Columns

function main(workbook: ExcelScript.Workbook) {
	let myTable = workbook.getTable("YourTableName");
	// Custom sort on table
	myTable.getSort().apply([
		{ key: 1, ascending: false },
		{ key: 3, ascending: true }
	]);
}

You can also sort by multiple columns with office scripts.

The above script will sort the 2nd column in descending order and then the 4th column in ascending order. You can use the same pattern to sort any number of columns in your table.

Sort Data with Sort Function

The last option to sort your Excel table is to get the data into Excel using the List rows present in a table action, then sort the results in Power Automate with the sort function.

Sort in Descending Order

@{sort(outputs('List_rows_present_in_a_table')?['body/value'],'Make')}

The above expression uses the sort function to sort the data on the Make column in ascending order.

Sort in Descending Order

@{reverse(sort(outputs('List_rows_present_in_a_table')?['body/value'],'Make'))}

The sort function has no option to change the sort order, but you can use the reverse function to achieve the same results.

The above expression uses the sort function to sort the data on the Make column in ascending order, then reverses the results using the reverse function. This is the same as sorting the data in descending order on the Make column.

Sort by Multiple Columns

@{sort(sort(outputs('List_rows_present_in_a_table')?['body/value'],'Make'),'Year')}

In order to sort your Excel table data on multiple columns with the sort function, you will need to use nested sort functions.

The above expression will first sort the data on the Make column in ascending order, then sort based on the Year column in ascending order.

Conclusions

Sorting an Excel table using Power Automate can be achieved in several ways.

To sort a table in ascending or descending order, you can use an OData query within the List rows present in a table action. This lets you specify the column and sorting direction, but unfortunately doesn’t allow for sorting on multiple columns.

If you require more complex sorting, you can consider using Office Scripts to sort the source data before importing it into your flow.

You can also use the sort function in your flow to sort any Excel table data after importing it into your flow.

Power Automate provides multiple options for sorting Excel tables. Have you used sorting with your Excel data in a flow? What did you need it for? Let me know in the comments!