How To - Automate Power Shell Data Source
Sometimes Power BI data source requires a csv to be populated by a Power Shell Script. Below are the steps to creating a source controlled Power Shell Script and automating the refresh with SQL Agent.
Instructions
Clone Git https://github.com/ucsb/ucsb-sa-powerbi
Open Visual Studio code and go to folder PowerShell/PBIDataSource
Create power shell script and push
Add power shell script to \sa8\data\PowerBI\PBIDataSource
The csv should be located in subject specific folder in \sa8\data\PowerBI\<subfolder>
Create SQL Agent Job with name in the following format: PBI - <descriptive name>
create a step with type Power Shell . The step will have a command line:
PowerShell.exe -File \\sa8\data\PowerBI\PBIDataSource\<scriptname>.ps1