Filter a an excel doc Power Automate Desktop

(0) Share

Report

Posted on by 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.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (0)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Zave 88 on at

Like
a
(0)

Report
Copy link

Link copied!
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:

Was this reply helpful? Yes No
XRQ57 206 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,
You can use Power Query for that.
Power automate can refresh the query itself.

Was this reply helpful? Yes No
Peterhui501 2 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
XRQ57 206 on at

Like
a
(0)

Report
Copy link

Link copied!

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

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

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No