Sep 05 2023 11:59 AM
Sep 05 2023 11:59 AM
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?
Sep 05 2023 12:23 PM
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.
Sep 05 2023 12:50 PM
Sep 05 2023 01:08 PM
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..
Sep 05 2023 01:21 PM
Sep 06 2023 02:18 PMSolution
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.
Sep 06 2023 06:25 PM