Unanswered

PAD Sorting Datatable

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello all,

I'm reading in data from an excel file but my flow will only work if the datatable is sorted by a specific column. I know I can manually sort the excel table itself but I'd rather have the ability to sort the data in app if possible. Is there any way to do this?

Thanks in advance!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (8)

Answers (0)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
I wish I knew how to sort a datatable, but I just don't see it. As a workaround inside of PAD, I am thinking of extract a column, because you can sort a %List% variable. Sort the list, and then do a for each on the list to create a new datatable, like this:
For each %CurrentItem% in %List%
For Each %CurrentItem2% in %OldDataTable%
If %CurrentItem2[3]% = %CurrentItem% #where Column 3 is the wanted column
Add Item %CurrentItem2% to %NewDataTable%
EndIF
End #For Each
End #For Each

It's a stupid workaround, but without a sort option on the datatable, I don't know how else you would do it. It's just re-writing the datatable in the right order.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hello @Anonymous

You can execute SQL query on Excel worksheet, as described in the following document:
Run SQL queries on Excel files - Power Automate | Microsoft Docs

With this method, you can use "ORDER BY" clause in SQL statement to sort by column you want.
Such like this:

SELECT * FROM [Sheet1$] ORDER BY [Name]

Sample flow:

Source Excel worksheet:

Datatable you will get as a result of SQL query (sorted by [Name] column in ascending order in this case):

Thank you.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, thank you both. I wish there was a cleaner way of doing this.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous, @MichaelAnnis
We can use the new DataTable actions to Sort on a single-column like this:

Was this reply helpful? Yes No
Damian_Marquith 2 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney - I just wanted to thank you for your example above. I had been trying to use the Index number generated in the 'DataTableMatches' output from the new 'Find or replace in data table' .. to pass through to identify fields in my second datatable. I kept getting errors every time I passed to the array... until I saw your example.. and saw the "Convert text to number" and then it clicked. Why would an INTEGER OUTPUT for a ROW INDEX BE AS TEXT ?? Anyway, thank you for showing this. Hopefully others will find this useful as well.

Was this reply helpful? Yes No
Malik5 on at

Like
a
(2)

Report
Copy link

Link copied!

You can use 'Run .NET script' action to Sort your Data Table variable in PAD.

Once the action is run successfully, you will have you Data Table variable sorted with the column name and sort direction you've provided, which in the example below is Rejected as column name, and asc as sort direction.

Was this reply helpful? Yes No
mcn24 5 on at

Like
a
(0)

Report
Copy link

Link copied!

@Malik5 May I ask which one is Data Table variable in your example? Thank you.

Was this reply helpful? Yes No
Malik5 on at

Like
a
(1)

Report
Copy link

Link copied!

Yes, pFVRs is the Data Table, it is a Flow variable, which is being used as an input and output parameter at the same time.

Thank you.

Was this reply helpful? Yes No