Automating Excel Power Query Actions

[Phishdawg]

I am an Excel neophyte.

I have learned how to transform Excel data from a source workbook to a destination workbook, and use Power Automate to move the transformed data to a SharePoint list.

I am in search of a way to automate the Power Query portion of the above.

Would I use the Excel 'Automate Work', or the 'Record Actions'. If yes to either, do I apply these to a desktop version of the two workbooks or do they work with online versions of the workbooks?

[#2]

@Phishdawg 

In Excel for web Power Query is refreshable only for sources at the same file, you can't update the query on another file or any other external source.

In general Automate which is interface for OfficeScripts is available on desktop version, but only on business subscriptions.

An option could be Power BI if you have Pro license. Excel with query could be published here if the query also loads data to data model. Here could be applied scheduled refresh or you may refresh it with Power Automate. Recently Microsoft announced some changes here, but I didn't play with them so far, thus just an idea.

[#3]

The source is an Excel Workbook that is received into an Outlook In-box several times a day/week.

Another individual has created a Power Automate flow that move the Excel workbook from the mail In-box to a SharePoint folder.

The workbook retains the exact same name every time it is moved to the SharePoint list. This seems to meet your clarification of - "only for sources at the same file".

I do have access to Excel 'business subscription'.

[#4]

@Phishdawg 

Okay, thank you. I only didn't catch what Power Query does. You said the goal is the transformation of one Excel file into another. Source file is moved from Exchange on SharePoint. Next you'd like to do some transformations within that file or transformed data will be put into another file? If the latest afraid OfficeScript won't help, it does its job within the file to which it is applied..

[#5]

To Clarify -

1. Once or twice a day (or several times a week) an employee receives workbook 'ExcelWorkboo.xlsx' in their Outlook in-box.

2. The name of that workbook never changes.

3. The employee has created a Power Automate flow that moves a copy of 'ExcelWorkbook.xlsx' to a SharePoint folder.

4. The workbook and four sheets. There are rows and data on all four sheets that is not need. I have used Power Query to transform the data in the sheets of 'ExcelWorkbook.xlsx', and saved the new data as a workbook in a SharePoint library.

5. I then use Power Automate to access the new workbook and migrate the data to a SharePoint list.

My question is whether there is a way to automate the Power Query action in the above, so that when the file is copied from Outlook to the first folder Power Query will automatically kick in and transform the new data in the source workbook (making the edits I made manually using Power Query), then add the transformed data to the destination workbook in the SharePoint folder, where Power Automate can move it to the SharePoint list.

[#6 · Accepted solution]

@Phishdawg 

Again, it's not clear where Power Query queries are created, They could be in mentioned ExcelWorkbook.xlsx file which first sent to employee and after by flow forwarded to SharePoint folder. If query works only with data within that file, it could be refreshed by OfficeScript run by Power Automate. If such queries work with external sources I see no way.

That could be another MasterWorkbook.xlsx, query within it calls data from ExcelWorkbook.xlsx, load transformed data into the grid of MasterWorkbook.xlsx and forward by Power Automate copy of the file into another location and under another name. If that's the case refreshing by Power Automate perhaps if only publish MasterWorkbook.xlsx on Power BI and query are to be loaded to data model as well. Not sure how this option works with recent changes in Poser BI Services.

Another option is not to use PowerQuery at all by do all transformations by OfficeScript applied by Power Automate to the ExcelWorkbook.xlsx. How feasible depends on concrete data within the file and what shall be an output.

[#7]

move the transformed data to a SharePoint list.
https://support.microsoft.com/en-us/office/export-an-excel-table-to-sharepoint-974544f9-94bc-4aa8-91....