I had the same issue. Here's the workaround I came up with (only tested in Power BI desktop):
1. Using Query Editor in PowerBI desktop, create a new query using R language as the data source (this data source is found in Other).
- The query using R as the data source basically invokes a PowerShell script that uses the ADAL library to authenticate with AAD and get a bearer token, which is usually valid for a small period of time
- PowerBI recognizes this query as a table. Let's say you name this table BearerTokenTable. This table will only have one cell, let's say the name of the single column is token. This cell will hold the value of the bearer token you obtain from AAD.
2. In your regular query where you call your AAD-authenticated API source, pass in the bearer token as the value of the Authorization header as below
Source = Json.Document(Web.Contents("<URL of your API>",
[
Headers = [Authorization=BearerTokenTable{0}[token]]
]
)),
Here is an example R script that invokes a Powershell script (in this case named aadClient.ps1).
aadData <- system("powershell C:\\aadClient.ps1", intern=TRUE)
BearerTokenTable<- as.data.frame(matrix(unlist(aadData), ncol=1, byrow=TRUE))
names(BearerTokenTable) <- c('token')And here's an example PowerShell script for the above (i.e aadClient.ps1)
# Import the required ADAL libraries
[System.Reflection.Assembly]::LoadFrom("${env: ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.IdentityModel.Clients.ActiveDirectory.dll") | Out-Null
[System.Reflection.Assembly]::LoadFrom("${env: ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll") | Out-Null
# Please make sure to use the appropriate values for your AAD tenant below
$LoginUrl = "https://login.microsoftonline.com/<AAD tenant>"
$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $LoginUrl
$AppId = "<your application id from AAD>";
$RedirectUrl = "<your AAD redirect url>";
$Resource = "<your API endpoint>"
# Acquire the token from AAD
$authResult = $authContext.AcquireToken($Resource, $AppId, $RedirectUrl, "Auto")
# This is important to output for the R script to capture
Write-Host $authResult.CreateAuthorizationHeader()
Make sure to use the right values for the various variables referenced in the PowerShell script above pertaining to your AAD tenant and your API endpoint (the loginURL, AppID and Resource). For more details on how the AAD authentication works see https://docs.microsoft.com/en-us/azure/active-directory/active-directory-authentication-scenarios#ba...
