Using PowerShell to download data for Power BI
I have recently been learning PowerShell, through the book "Learn PowerShell in a Month of Lunches". It's a great book, and it's living up to the title. It's geared towards system administrators, so there are parts of the book that I'm skipping over, but the skills I learned there really came in handy this week when I had to come up with a workaround for some data extraction for Power BI.
What the problem was:
One of the Power BI reports I work on pulls data by connection to an external REST API. The connection works just fine in Power BI Desktop. I can see the data coming in and I seem to authorize myself correctly. However, it would fail to authenticate when published to Power BI Service.
I had a vague idea that might be able to use curl, to pull in data from the API, so I went ahead and tried it out.
The workaround
Summary
The strategy that I took was to download the data through the API to a "local" file using a PowerShell script, and have Power BI read from that.
I didn't want to trigger that download manually, so I hooked it up so that the script would run every time I started up my work laptop (the shell script is stored locally on the machine).
To break it down...
"Download the data"
I know about curl, and that keyword does work on PowerShell, but it's actually an alias for the cmdlet Invoke-WebRequest.
I lined up all my targets in an array, and used Invoke-WebRequest to hit them all in turn, and redirect them into a text file. The script is below.
I'm using the full cmdlet names below, but in real life, I just the aliases.
$root = <root>
$key = 'Basic <key>'
$targets = "/target1", "/target2", "/target3"
ForEach ($target in $targets) {
$name = $target.split('/')[-1]
Write-Host $name
$raw = Invoke-WebRequest ($root+$target) -Headers @{"Authorization" = $key}
$content = $raw.Content
$content > ".\$name.json"
}
The data downloaded is exactly what Power BI would have received had it made the HTTP request itself. The Power Query script was already expecting this format and it would have required less effort for me to modify the query.
The files has to be stored somewhere off my local machine so that it could be accessible anywhere. I saved it to a SharePoint location that is synced on my machine. Note that the PowerShell script creates the file in the same location where it is run. Thus, the script needs to live in the same location as the place that Power BI is looking at.
Automation
I don't want to have to double-click this PowerShell script every time it needs to be updated. It's not about the number of minutes it takes me to do this, but the mental burden of having to remember it and switch context whenever I do.
I decided to make it run whenever my computer was started up. I could run PowerShell commands using shortcuts, but including the -command flag.
If I place that shortcut in shell:startup (in the startup folder), then it would run whenever my computer starts up. This way, I don't need to remember to do it.
I created a blank shortcut in the Startup folder and placed the following target:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command "cd 'C:\Users\dtanuwibaw\TECHNIPFMC\Genesis Lessons Learned - Prism_Data'; .\Get-PrismLessons.ps1"
Notice that I target the actual PowerShell executable, but with a `-command' flag. The string that follows is treated like as if it was written into the console.
Notice also that I actually move the shell into the folder location where the script was located - because of the space characters in the path, I didn't know how to run it on the spot without errors being thrown up.
Success?
This is a bit of a hack, but for all intents and purposes, my Power BI report is now back to normal, and all fixed in an afternoon.
I note some weak points below.
Weak points
The data doesn't get updated if I don't turn on my computer. This update is dependent on me.
The script to run this automatically has no hardcoded path - it will dump data files wherever it lives. However, the shortcut that enables the automation is hardcoded. In order for someone else to run this, they would need to have the exact SharePoint folder synced on their local machine, and replace the directory path to the one on their machine.