Unanswered

How to clean up large Excel files in PAD

(0) Share

Report

Posted on by LyCo

I have a project where I need to filter a huge file with 50'000 entries so that only the relevant ones remain (remove everything where column C has value 98 for example).

If doing it just with PAD (with the inbuilt actions) it takes more than two hours.

What's the best/most used way to handle such files?

Categories:

Power Automate Desktop

All responses (3)

Answers (0)

Sort by

Benny_1857 124 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi LyCo, what you request is a Excel Power Query problem. Once you made the PQ, you could run it by PAD everytime automaticly.
2 sheets. Sheet A: Data sheet(>50,000 rows) and Sheet B: Filter sheet (filter what every you need to filter)
Load 2 sheets into Excel Power Query
Merge A with B LeftAnti to filter only A has
Close and Load
Then use PAD to trigger this excel to refresh
You could change the data of Sheet A and filter condition of Sheet B every time if needed
If you need the screenshotcut step by step please let me know.
LyCo 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Benny

Ok, thanks! Never really got into Power Query, but might be the right time to dive into it.

And to refresh I would just need PAD to open and close the file?

Thanks again!
Benny_1857 124 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes，once you made it, next time you just need to let it load the new data and do every steps automatically.
And you just need to use PAD to open the excel file. click Data, Click refresh all.
Then wait 2-3 second. Because to launch will be about 2 sec at least.
Add an image wait after that for "ready" because depends on the data volume and steps you have the time will be 3 - 2 mins. That image wait is very useful as you do not know how long you need to wait.
By the way, PQ is not so hard to learn. At least, 50 hours learning for most buttoms on the tools bar is enough for a beginner. The most M language you just need to change but not write.