Power BI REST API: How to get authentication token with PowerShell Cmdlets
UPDATE: If you run into issues like “Install-Package : No match was found for the specified search criteria and module name“, that might be due to TLS 1.0 and 1.1 being deprecated.
Details here: https://devblogs.microsoft.com/powershell/powershell-gallery-tls-support/
The code below was updated to include this change!
Using the Power BI REST API is a great way to start to automate stuff if you are a Power BI administrator or if you are a geeky Power BI user that wants to get the most out of this BI piece of software.
Cool things that you can do by using the Power BI REST API:
- Trigger an import refresh from outside Power BI Service: good with integrating with other flows like ETL jobs or processes orchestrators
- Checking the status for a Power BI data source: see if the data source connection is still working as expected
- Generate Power BI objects lists: export the list of workspaces/groups, list of reports/dashboards/datasets, etc.
So, we’re off to a good start having the above cool things that we can achieve with the Power BI REST API in mind.
The starting point of any good development is to read the documentation. The Power BI REST API documentation is available, although is not very easy and intuitive to get ones head around it (this seems similar for all Microsoft products). The documentation page offers few examples, easy ones, but not very helpful when coming to provide details or insights. For that I always turn to the Power BI Community which most of the times provides all the details missing from the documentation.
Right from the start, on each and every Power BI REST API endpoint page, there is a section where we see specified the Required scope and link to Register an app. This app will help you in getting the access token that you will need for accessing the REST API endpoints. If you are familiar with Azure apps, this may seem straight forward for you, congrats – I envy you! But for me, the fact that I don’t fancy Microsoft products very much because they are too interconnected, and due to my BI background, registering an Azure app and requesting the access token was not a very easy thing to achieve. Managed to get my head around the problem in the end and successfully used the REST API, but that was no walk in the park.
If you are already struggling with getting the access token, continue to read below. Else, kudos, you don’t need below info unless you want to switch to using the Power BI PowerShell Cmdlets to request the access token.
Getting the access token, the easy way!
So, as I said above, for accessing any Power BI REST API endpoint you will need an access token. Here is how to get the access token via PowerShell:
1. Make sure you have PowerShell installed on your machine. If you don’t have it, use this Installing Windows PowerShell documentation to get PowerShell up&running on your Windows machine.
2. Open PowerShell as Administrator (Run as Administrator or you won’t be able to install the module) and install the Microsoft Power BI Management module
|
1 2 |
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 Install-Module -Name MicrosoftPowerBIMgmt |
3. Import the Power BI Management modules
|
1 2 |
Import-Module MicrosoftPowerBIMgmt Import-Module MicrosoftPowerBIMgmt.Profile |
4. Have a user that has access to Power BI ready for the login step.
Warning! The user should not have any MFA (Multi Factor Authentication) enabled. Also, Non-Federated accounts are preferred. Talk to your AAD (Azure Active Directory) admin about these if you have issues in authenticating with your user.
|
1 2 3 4 5 6 7 8 |
$password = "your_password" | ConvertTo-SecureString -asPlainText -Force $username = "email@company.com" $credential = New-Object System.Management.Automation.PSCredential($username, $password) Login-PowerBI -Credential $credential $headers = Get-PowerBIAccessToken $headers_string = Get-PowerBIAccessToken -AsString |
5. If above step succeeded and you have not received any red error message saying “Login-PowerBI : Failed to get ADAL token […]” then you are one step away from getting your access token. At this point the access token is stored inside the $headers_string variable as string and inside the $headers variable as object. My personal preference from here is to get the access token ($headers_string > token.txt) and to use it from more HTTP-like clients or from Postman while developing, but if you are comfortable with PowerShell you should be able to use the access token “as is” by continuing from within PowerShell to query the Power BI REST API.
The access token is something like Bearer xxxxx… and should be used inside the Authorization header of the HTTP requests.
Here is one example to get the list of all groups/workspaces that the user you are authenticated with has access to:
|
1 |
$result = (Invoke-WebRequest -Uri "https://api.powerbi.com/v1.0/myorg/groups" -Headers $headers -Method GET).Content |
Full working example
Here is the full code we’ve written above:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 Install-Module -Name MicrosoftPowerBIMgmt Import-Module MicrosoftPowerBIMgmt Import-Module MicrosoftPowerBIMgmt.Profile $password = "your_password" | ConvertTo-SecureString -asPlainText -Force $username = "email@company.com" $credential = New-Object System.Management.Automation.PSCredential($username, $password) Login-PowerBI -Credential $credential $headers = Get-PowerBIAccessToken $headers_string = Get-PowerBIAccessToken -AsString $headers_string > token.txt $result = (Invoke-WebRequest -Uri "https://api.powerbi.com/v1.0/myorg/groups" -Headers $headers -Method GET).Content $result |
Here is the output of the code (some details are stripped out from the snapshot for confidentiality):
Other resources
Try the Power BI REST API with no coding needed. Follow the guidelines here: https://azure.microsoft.com/en-us/updates/power-bi-rest-api-tryit-tool/
Found this YouTube video that gives an overview on the Power BI REST API and the available endpoints and how to use them:
Ending note…
As I mentioned, using the Power BI REST API is not so straight forward, at least for me it wasn’t as I needed to dig and read a lot of different articles and community posts and the most important of all, I tested A LOT!!
Hardest part for me was to get the authentication in place by requesting the access token. Hope it’s going to be easier for you after going though this example.
Did you like this article? Share it with your fellow Power BI friends!
