On Power BI Desktop I am connecting to a REST API that exposes company data requried for reporting. The API expects a bearer token for authentication. I have used the following approach to generate the bearer token and then inject it into the API call to retrieve data. This works fine on Power BI desktop but fails after publishing to Power BI Service. While this works on Power BI desktop I am wondering if this is a limitation to Power BI Service and if this is the best approach.
- Create a function called GetBearerToken includes the following logic:
() =>
let
clientId = "{your_client_id}",
clientSecret = "{your_client-secret}",
tokenUrl = "{token_server_url}",
body = "client_id=" & clientId & "&client_secret=" & clientSecret & "&grant_type=client_credentials",
headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
tokenResponse = Web.Contents(tokenUrl, [Headers=headers, Content=Text.ToBinary(body)]),
tokenJson = Json.Document(tokenResponse),
accessToken = tokenJson[access_token]
in
accessToken - Inject the results of the GetBearerToken function into the request to get data from the REST API (M Query)
let
Source = Json.Document(Web.Contents("{rest_endpont_url}", [Headers=[Authorization="Bearer " & GetBearerToken()]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "contact", "address1", "address2", "city", "state", "zipCode", "phone", "fax", "email", "searchCode"}, {"id", "name", "contact", "address1", "address2", "city", "state", "zipCode", "phone", "fax", "email", "searchCode"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"id", Int64.Type}, {"name", type text}, {"contact", type text}, {"address1", type text}, {"address2", type text}, {"city", type text}, {"state", type text}, {"zipCode", type text}, {"phone", type text}, {"fax", type text}, {"email", type text}, {"searchCode", type any}})
in
#"Changed Type"
I am using a FROM WEB data source with anonymous credentials since the bearer token is being injected into the M Query at runtime. When I publish to Power BI Service and attempt to manually refresh the data or schedule a data refresh, I receive the following error.
Scheduled refresh is disabled because at least one data source is missing credentials.
Is this a limitation of Power BI Service or is there a better approach for retrieving data from a REST API that requires authentication via JWT?
