power query and Excel "Refresh All" automation

[nravulapalli]

I am using power query as a desk top ETL tool to load data from several sources. I am breaking up business logic in to heirarcy of excel spreadsheets. I am  would like to automate the sequece on data loading and "Refres All" with out manual intervention. These files may not be open at the time of updates. Following is logical view

1. Set of source files (Raw data)

2. Set intermediate files that does ETL on the Source files (Raw data) to create data model and needed data (these files may have business logic throgh formulas in addition to power query, no VBA at this point)

3. Set of Caculation files that consumes Intermediate files using power query (mostly buisenss logic throgh excel fomulas, noVBA at this point)

4, Out bound extracts created from the calcualted values using Power Query as ETL

All is working great manually.  I want to able to execute data refeshes in programmatic way and in a sequece I want to control.I tried to use "Refresh All Connection Properties", auto refesh seem to work only if the file is open.

Should I be looking at PowerShell scripting or any other technologies that can help with this? 

Thanks in Advance

Niranjan

[markwink]

Try using Power Update - I have been trying the free version for a couple of weeks. It seems to leverage MS Task Scheduler, impressed withthe email notification I recieve to let me know the workbook has been updated sucessfully.

Introducing-power-update

Claims : Schedule the refresh of your Power Pivot Workbook as frequently as desired, Auto-Publishes to any location, and  Sends  you an Email Notificationof Success/Failures

[HakanSoderbom]

There are partner tools that specifically focus on simple automated workflows for data prep for Power BI. One such example is Alteryx. You can read up some more here https://powerbi.microsoft.com/en-us/blog/alteryx-power-bi-visualizing-the-results-of-alteryx-workflo... and here http://www.jenunderwood.com/tag/alteryx/.

While the above resources focus on the direct output from Alteryx to PowerBI.com you can as easily put the data into SQL, Access or Excel for further use in e.g. Power BI Desktop.

Best of luck

/Håkan Söderbom, Tech Alliances, Alteryx

http://alteryx.com/microsoft

[Greg_Deckler]

Well, this is quite old school and a complete hack but we used to do this with Excel VB Macro's back in the day, in code do a refresh all and then an export. I am not aware that any such similar capability exists in Power BI Desktop. You could automate the refresh by using the service, I would recommend putting your PBIX file on OneDrive and using the "Connect" option (versus import). I am not aware of a way to automate export at all currently.

I suppose it goes without saying that this is not exactly the intended use case for Power BI Desktop (just like it was never the intended use case for Excel back in the day).

[nravulapalli]

Thank you for your response.

To clarify, I am not using this with PowerBI. It is purely excel files on desktop / local server. It is an internal enterprise app.

Just Exel and Power Query

[Greg_Deckler]

Oh, well then you want the VB macros, let me check for those to see if I still have them. Ah yes, here's the basic approach:

1. Dedicate a workstation running Excel 2013 to the reporting update process
2. Save the file as “Macro enabled workbook” (.xlsm)
3. Enable the Developer tab, go into VB and paste the following code (or equivalent)

Sub Auto_Open()

Application.Wait (Now + TimeValue("00:00:10")) ' Allow 10 seconds to cancel when opened so we can edit if necessary

Application.DisplayAlerts = False ' Disable prompts for saving over existing files, etc.

strPath = "C:\temp\Book1.xlsx" ' Set path to saved xlsx file

ThisWorkbook.RefreshAll ' Refresh the data model

ActiveWorkbook.SaveAs strPath, xlOpenXMLWorkbook ' Save xlsm file to xlsx file

Application.Quit ' Quit application

End Sub

What this code does is that when the Excel file is opened, it refreshes all data connections and saves the workbook as a “.xlsx” file (not macro enabled).

1. Create a scheduled task on the dedicated workstation to open the Excel file at a set interval
2. Based upon the previous set interval, schedule a second task to allow enough time for the data model to refresh and the file to close. This second task copies the .xlsx file to the document repository. There are a number of methods available to achieve this including mapping a drive to the document library and performing a simple copy, etc.

The above is copy paste from an approach document that I wrote and we successfully implemented, I forget the details around it but you could replace the saving as XLSX file to whatever export you want.

Please understand, this is a REALLY hacky approach as this was never an intended use for Excel. I only ever use this when forced to by circumstance and it is never a "recommended" option. I would hold to my recommendation to sign-up for Power BI Service, Connect your Excel spreadsheets and allow it to do the refreshes, they should work with Power Query queries no problem. On the automated export side, I don't really have a solution for you.

[nravulapalli]

Thanks for your soultion details. I will play with it and let you know my progress.

[fbrossard]

Power Query creates Workbook connections that can also be refreshed on open.

You can find the relevant Power Query-generated connections in the Workbook Connections dialog under 

Data -> Connections, and configure the highlighted settings:

This VBA will also refresh Power Query data:

ActiveWorkbook.RefreshAll

[nravulapalli]

Thaks for your response, I am already using Connection Properties. I am trying to avoid the users having to open and close the all the files to refresh the data. I have close to 10 intermediate, calc and output files

I am going the route of using powershell script automation to automatically open, refresh all and close the files in the sequence I want them. I will update the details after I am done with powershell scripting.

Reagrds,

Niranjan

[Cbischel]

HI nravulapalli, 

Did you ever get this to work in Powershell? I was hoping to find a similar solution in powershell and wanted to see if you built any code that worked and you would be willing to share with us.

[mike_honey]

From my experience, it does seems important to disable any other Excel Add-Ins (besides PQ). Otherwise Excel tends to freeze a bit.