Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
threw001
Helper II
Helper II

Advice please - Use Power Query to replace/remove the need for RPA and Access DB

Hi all,

 

I have a process that uses a mix of Access Database, Power Automate Desktop and Power Automate Cloud. I would like to see if there was a way to come up with a similar output using a different process, more specifically without the need of having RPA and an online/available enviroment for the automation to run (cutting out Power Automate Desktop).

 

Please see below process steps:

Step 1) I have data that is sent to me via email attachment (xlsx), Power Automate Cloud is triggered whenever this email is received to take the attachment and save it into a certain location under a certain file name (write-over).

Step 2) This file is plugged in as a data source for Access Database, a bunch of joins and logic tests are done in Access and there are output queries set up.

INFO I have a master excel file that uses Access Database Queries as data source (my final data outputs) into multiple worksheets within the same workbook.  

Step 3) Power Automate Desktop flow is triggered, it logs into my machine which is specically set up to be online at all times and available. It logs into my user, opens the excel file, navigates to the ribbon and presses "Refresh All".

Step 4) The refresh happens, the file is saved.

Step 5) Power Automate Cloud then picks up the saved file, saves a copy of this file into a SharePoint folder location

Step 6) Power Automate Cloud then sends me an email notifying me that the flow has ran successfully.

 

The main output of the process is to produce a file that takes data (from the email), use set custom rules/logic and apply to the data. Manipulate the output and then save this into multiple locations.

My main objective is to get away from having to use Power Automate Desktop where RPA happens. I am finding that sometimes the flow fails due to either not having an enviroment available for the flow to run in, etc. I would like the automation to be fully cloud based to increase reliability.

Can this be done in Power Query with a mix of Power Automate Cloud?

 

Please advise 🙂

 

Thanks,

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Hi @threw001 

 

You will have to see which trigger is most suitable for your Cloud flow, but you can always schedule a recurrence... Next run an OfficeScript to Refresh All Connections within your Excel file. Learn how to Run Office Scripts with Power Automate, here.

 

 Here's the script. You can find OfficeScripts on the Automate tab of the ribbon in Excel.

 

function main(wb: ExcelScript.Workbook) {
	// Refresh all data connections
	wb.refreshAllDataConnections();
}

 

I hope this is helpful

View solution in original post

4 REPLIES 4
m_dekorte
Super User
Super User

Hi @threw001 

 

You will have to see which trigger is most suitable for your Cloud flow, but you can always schedule a recurrence... Next run an OfficeScript to Refresh All Connections within your Excel file. Learn how to Run Office Scripts with Power Automate, here.

 

 Here's the script. You can find OfficeScripts on the Automate tab of the ribbon in Excel.

 

function main(wb: ExcelScript.Workbook) {
	// Refresh all data connections
	wb.refreshAllDataConnections();
}

 

I hope this is helpful

Hi @m_dekorte 

The OfficeScript is working great!

The only thing I am finding now is that Excel is asking me to sign in everytime I open the file. (I think due to the data connection). 

Is there an office Script to delete all data connections?

Thanks! 

Hi @threw001 ,

 

Just copy the output from your worksheet into a new xlsx file with OfficeScripts and Power Automate

 

Doesn't match your scenario exactly but for inspiration check out this post:

https://exceloffthegrid.com/move-data-between-workbooks-power-automate/

 

I hope this is helpful

Thank you! I am now using this with Power Query, cutting out RPA and DB Access

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors