PAD Sorting Datatable

[tcall]

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!

[MichaelAnnis]

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.

[Anonymous]

Hello @tcall 

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.

[tcall]

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

[mdevaney]

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

[Damian_Marquith]

@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.  

[Malik5]

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.

[mcn24]

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

[Malik5]

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.