Answered

Using Power Automate to save a weekly emailed sales report attachment, run a Power Query, save resulting file to OneDrive

(0) Share

Report

Posted on by Matthew_Simpson

How could I use Power Automate to receive weekly sales report (email attachments) and then perform data cleansing and save the resulting CSV file into a OneDrive folder?

Obviously there are multiple ingredient to a flow like this but does anyone know how I could start building this?

Thanks,

Matthew

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Matthew_Simpson

I don’t think you can use power query via a flow yet but you could investigate office scripts. Excel has a recording tool allowing you to record and write a typescript function and call it from power automate. The beauty of it is that excel data doesn’t need to be in a table and you can format it, sum it, create charts etc.

I’ve a few videos on my YT you can look at https://www.youtube.com/playlist?list=PLzq6d1ITy6c2_qM_ocYDtEaENrqi92YmM or read official docs here https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No
Matthew_Simpson 9 on at

Like (0)

Report
Copy link

Link copied!

Thanks Damo, yep this is the way to do it. I haven't perfected the solution yet, but I am closing in on it. If you would like extra brownie points, can you point me to a way to get information in a cell/cells from the weekly report and use as part of the filename when resaving the transformed report?

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Matthew_Simpson

You would use the expression return(). It will then include the data you have passed to it in the function and allow power automate to query it from the output of the script.

Damien

Was this reply helpful? Yes No
Matthew_Simpson 9 on at

Like (1)

Report
Copy link

Link copied!

Damien, you're on fire. This is going to be fun. Thanks again.

Matthew

Was this reply helpful? Yes No