Forum Discussion
Automating Excel Power Query Actions
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.
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'.
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..
- 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.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.
