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
