Forum Discussion

Phishdawg's avatar
Phishdawg
Brass Contributor
Sep 05, 2023
Solved

Automating Excel Power Query Actions

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 ...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 06, 2023

    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.

Resources