Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
Expand all | Collapse all

Excel power query refresh without opening the file

  • 1.  Excel power query refresh without opening the file

    Bronze Contributor
    Posted Feb 10, 2021 06:05 PM
    Hi Everyone,

    Currently I have a flow that takes input parameters from a canvas app, and updates an excel file with those parameters, which will then be passed into a couple of power queries within the same excel file. I was wondering if it was possible to have the queries refresh by themselves in the background with those parameters without the user having to open the file to refresh the queries.

    Thanks,
    -Jack

    ------------------------------
    Jack Kao
    ------------------------------


  • 2.  RE: Excel power query refresh without opening the file

    Bronze Contributor
    Posted Feb 11, 2021 08:44 AM
    Hi Jack,
    I am looking to do the same / similar, but currently don't need to update parameters. I do not think it is currently possible, however: if the refresh can be performed in Excel Online, then you may be able to use Office Scripts (which are very new) to perform your refresh. You can call an Office Script from a flow. You can "record" an Office Script like a VBA macro and edit it after that.
    Caveats are these: Office Scripts are new, and a "preview" feature. Also, not all data connections can be refreshed in Excel Online. In fact, none of the ones I'm looking to automate can be refreshed online (one uses an ODBC connection to an Oracle server, the other connects to CSV text files on a network server).- so Office Scripts can't do what I want.
    Your mileage will vary though - good luck!

    ------------------------------
    Rob Clark
    Cambridge
    ------------------------------



  • 3.  RE: Excel power query refresh without opening the file

    Posted Feb 11, 2021 12:49 PM
    I haven't found a way yet to do a refresh via flow.   I'd like it as I have several excel files with powerquery on shareponit acting as intermediaries between access databases on our lan and Power BI in the cloud.   But so far I have to manually refresh each.  I'm all eyes if anyone has a solution for that.

    ------------------------------
    Elena Schott
    Sr. Business Analyst
    Aon - San Francisco, CA
    ------------------------------



  • 4.  RE: Excel power query refresh without opening the file

    Posted Feb 12, 2021 11:18 AM
    You can try using the RPA  tool to record a desktop macro that pushes the buttons in excel .. we do a similar approach for web apps that don't have APIs.

    The windows macro capability is pretty strong

    ------------------------------
    Craig Yappert
    VP of Information Technology
    ------------------------------



  • 5.  RE: Excel power query refresh without opening the file

    Posted Feb 15, 2021 12:11 PM
    Thanks, I had wondered if that might be next, or maybe the old tool PowerUpdate from PowerPivotPro.   It's good to hear it's working for you.   I'll give it a try.

    ------------------------------
    Elena Schott
    Sr. Business Analyst
    Aon - San Francisco, CA
    ------------------------------



  • 6.  RE: Excel power query refresh without opening the file

    Silver Contributor
    Posted Feb 15, 2021 02:54 PM
    Hi Jack,

    Sounds like this is what you are looking for, Power Update. This was originally a collaboration between PowerPivotPros and PowerONBI.

    Power Update - Power ON BI

    Mike

    ------------------------------
    Mike Hammons
    Director, Business Intelligence
    AKA Enterprise Solutions
    ------------------------------