Filter a an excel doc Power Automate Desktop

[Peterhui501]

I have a doc of 65000 rows.

How do I filter this using power automate desktop?

Seems like there isn't a filter function or anything like that in the excel section. Currently I open the excel file, and I manually filter it (just one column) and then close it and I let power automate do things after.

It would be easier if I didn't have to any of that. 

Looping seems to take sooo long! There must be a easier way. 

[MichaelAnnis]

Use hotkeys to set up the filter. I am not at my computer, but you can do it manually to figure it out. 

In Excel, press Alt, then press the navigation keys shown on the menu to access the filter. Use tab, arrow keys, and other keys as needed to set the filter. 

You can have PAD do all this through Send Keys. 

[Zave]

Hi,

You could also use SQL to retrieve the columns you want from an excel file (or any other information you want really).

For example, the following flow will retrieve the column "First name" from the excel file "Book1.xlsx" from the sheet "Sheet1":

Folder.GetSpecialFolder SpecialFolder: Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> SpecialFolderPath
SET ExcelFile TO $'''%SpecialFolderPath%\\Book1.xlsx'''
SET ConnectionString TO $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%ExcelFile%;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";'''
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: ConnectionString Statement: $'''Select [First name] from [Sheet1$]''' Timeout: 30 Result=> QueryResult

 The excel file is saved on my desktop and the result for the above query is:

[XRQ57]

Hi,

You can use Power Query for that.

Power automate can refresh the query itself.

[Peterhui501]

How do you call power query from power automate desktop? There is the " launch application" option, but I don't want to go through all the steps just to filter a spreadsheet. So far the key strokes seem easiest

[XRQ57]

With PAD using a template file.

1 -Open the template Excel file

2 - Open the data file

3 - Copy the data in the template

4 - Refresh data ( Request Power Query created beforehand in the template file) => ALT, é , L, T

5 -Save File

6 - Close Excel

[Anonymous]

Can you please add more details (step-by-step) to this?

Thank you

[momlo]

I'm always a fan of SQL in such cases - clean, fast, usually 3 actions: open connection, query, close connection

@Peterhui501  take a look at @Zave 's and below:

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel