Hello everyone,
I'm experiencing some challenges with connecting to a REST API in Power BI, and I would greatly appreciate any guidance or suggestions.
Power BI Desktop: I can successfully fetch data from the API, but the loading time is excessively long (over 10 minutes).
Power BI Service: The data fetching does not work at all. I get this error:
Error message: DataSource.Error: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.. ;Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.. The exception was raised by the IDbCommand interface.
- It seems that the size of the data I’m fetching might be the issue. For example, if I only retrieve the file ID without the content, it works fine in Power BI Service. However, fetching the full content results in failure. The data I am fetiching is a excel workbook (22 MB). As I said above, Power BI Desktop works fine with content as well.
Some background information:
I have 3 queries:
- get_token: to get the token for the API Authentication (as a function)
let
GetAccessToken = () =>
let
url = "<>",
headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
#"Authorization" = "Basic Og=="
],
body = [
grant_type = "password",
client_id = "<>",
client_secret = "<>",
username = "<>",
password = "<>"
],
// Convert the form data into a URL-encoded string
content = Text.ToBinary(Uri.BuildQueryString(body)),
// Make the POST request
response = Web.Contents(url, [
Headers = headers,
Content = content
]),
// Parse the JSON response
jsonResponse = Json.Document(response),
// Extract the access token
accessToken = jsonResponse[access_token]
in
accessToken
in
GetAccessToken
- get_data: with the token getting the data but crypted base64:
let
GetData = (token as text) =>
let
// Define the GraphQL API URL
url = "<>",
// Set up the headers, including the Authorization with the Bearer token
headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & token,
#"User-Id" = "<>"
],
// Define the GraphQL query in the body
body = "{""query"":""query getCustomerEvaluationData { customerEvaluationData { file { id content } generatedAt } }""}",
// Make the POST request to the GraphQL API
response = Web.Contents(url, [
Headers = headers,
Content = Text.ToBinary(body)
]),
// Parse the JSON response
jsonResponse = Json.Document(response),
// Extract the relevant data (if required, you can navigate deeper into the response)
customerEvaluationData = jsonResponse[data][customerEvaluationData],
file = customerEvaluationData[file],
// Extract the Base64 content
base64Content = file[content],
// Decode the Base64 content to binary
binaryContent = Binary.FromText(base64Content, BinaryEncoding.Base64)
in
binaryContent
in
GetData
- get_excel: using the queries above to get the Excel:
let
// Call the get_token function to get the access token
token = get_token(),
// Call the get_data function to get the binary content of the file
binaryContent = get_data(token),
// Load the binary content as an Excel workbook
excelWorkbook = Excel.Workbook(binaryContent, null, true)
in
excelWorkbook
Is there a limit for Power BI Service thatit can not handle a 22 MB Excel WorkbookAPI fetch? or are my codes wrong? what can i do? (I need power bi service for scheduled refreshes).
Many Thanks for you help!
