Using PowerShell and the Power BI REST API

April 9, 2019 - 4 minutes read - 838 words

Introduction

I think that the Power BI REST API is one of the most overlooked features of Power BI. It is an immensely powerful tool that can save you a lot of time when it comes to maintaining a large number of reports. Especially if you do not have access to some of the admin tools within your organisation.

While a REST API can look like an intimidating task it is far easier than you may think to get up and running with the API.

First a tiny bit of background, you can read very technical and wordy definitions on what a REST API truly is but I will summarise it quickly here. A REST API is a way to send requests between systems, in this case to PowerBI.com. These requests in general can be split into two types either be to tell the system to do something or to send us something. You do not have to use PowerShell to integrate with the REST API, you can use any language which supports web requests.

Setup

In order to get setup from the Power BI standpoint you need to register an App, there is a very good blog post on this by Radacad so I recommend you go over there set that up then come back to this post. Once you have that setup make sure you have your client id as this is how Power BI will know to authenticate you in order to use the API.

The next step is to install what is called the AzureRm cmdlet. All you need to know is that this lets us connect to Azure and authenticate into Power BI. To do this you need to launch PowerShell as an administrator, if you are on Windows 7+ you will already have it installed. If you are on Mac or Linux you can install it manually, if you are on Windows Vista you should consider upgrading and getting out of 2007.

To launch it search for it in the windows bar and click run as administrator. Once you have PowerShell launched you should have a nice blue window

Next we are going to install the cmdlet, this can be done by navigating to this PowerShell Gallery link then copying the Install Module command. It should look something like “Install-Module -Name AzureRM”. Once you have this paste it into PowerShell, follow through the prompts till it is installed. Once this has gone all the way through you now have everything installed to use the REST APIs through PowerShell.

The scripts

For this post I will cover just two useful scripts that deal with setting up refreshes for datasets in PowerBI.com. The first lets you easily set the refresh schedule of a dataset. Of course you can do this directly in the service which is fine if you have one dataset and one refresh time. However, if you need to set several datasets to refresh 48 times a day it can take a long time and a lot of clicks to set it up.
The second will stagger the refreshes of datasets within a workspace over a 24 hour period.

To start using the scripts first you need to download them from GitHub, all these scripts are located here and any future changes or new scripts will be shared there as well.
Next you need to make some slight edits to the scripts. You can either do this with just a text editor like notepad or using more advanced editors like
VS Code (what I use) or the inbuilt ISE.
Once you have the file open, fill in the parameters such as workspace id, dataset id etc. You also need to put your client id in the line where it says

$clientId = ""

Insert your client id between the quotes, once this and all the needed parameters are filled in you should be able to run these scripts and they should change your refresh schedules accordingly.

Troubleshooting and further info

A common error that people get is

Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'file:///C:\Program Files\WindowsPowerShell\Modules\AzureRM.profile\5.8\Microsoft.IdentityModel.Clients.ActiveDirectory.dll' or one 
of its dependencies. The system cannot find the file specified.

If you get this error, make sure the cmdlet paths in the script are correct. Where it says

    $adal = "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.profile\5.8\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
    
    $adalforms = "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.profile\5.8.2\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"

Navigate to that location and check that the files are present. If you are reading this in the future make sure that the version number in the path “…ofile\5.8.2\Micro…” is correct. As if you have a newer version installed you will no longer be on version 5.8.2.

You can find more scripts for the REST API, which these scripts were based on, on the Azure-Samples github. Make sure you check these out as you may also find these useful.

If you have any feedback, questions, either about this post or the Power BI REST API in general, please leave me a comment below and I will endeavour to get back to you.