cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matthew_Simpson
New Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

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 😉

View solution in original post

4 REPLIES 4
DamoBird365
Super User
Super User

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 😉

View solution in original post

Matthew_Simpson
New Member

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?

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

Matthew_Simpson
New Member

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

 

Matthew

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,082)