API Call with R works on PowerBI-Desktop but not in PowerBI-Service

RAnalytics — Thu, 06 Jul 2023 09:51:58 GMT

Hello everyone, hope I'll find someone who can help me here.

I've created an R-Script which calls an API that should get the latest ticket from the selected customer. Everything works fine when I run the Script on Power-BI Desktop - I receive the output and can visualize it in the Report. However, when I upload the report and open it in Teams/Browser I receive the following "Error in curl::curl_fetch_memory(url, handle = handle) : Could not resolve host: subdomain.zendesk.com".

There is no Proxy and we do not have Premium-Capacity. Has someone else faced this issue?

Code is (but I don't think that this is the problem):

# Der folgende Code zum Erstellen eines Datenrahmens und zum Entfernen doppelter Zeilen wird immer ausgeführt und dient als Präambel für Ihr Skript:

# dataset <- data.frame(LeadName)

# dataset <- unique(dataset)

# Fügen oder geben Sie hier Ihren Skriptcode ein:

library(base64enc)

library(gridExtra)

library(grid)

library(dplyr)

library(httr)

library(jsonlite)

library(tibble)

# Set Zendesk API credentials

subdomain <- "subdomain"

api_token <-"token"

headers <- c(

"Authorization" = paste0("Basic ", api_token),

"Content-Type" = "application/json"

)

# Define the words to remove

words_to_remove <- c("EURO", "VTP", "\\*VTP", "VERTRIEBSPARTNER", "VP", "ue", "UE")

# Create the regular expression pattern

pattern <- paste0("\\b(", paste(words_to_remove, collapse = "|"), ")\\b")

# Remove the specified words using gsub()

name_cleaned <- gsub(pattern, "", dataset$LeadName, ignore.case = TRUE)

organization_name <- URLencode(name_cleaned, reserved = TRUE)

datalist = list()

obj = 1

# Get Tickets from Organizations #################################################################################################

# Create API Call URL

url_org <- paste0("https://", subdomain, ".zendesk.com/api/v2/organizations/autocomplete?name=", organization_name)

# Perform the API call

response_org <- GET(url_org, add_headers(headers))

# Check if the API call was successful

if (response_org$status_code == 200) {

# Extract the response content as JSON

response_json <- jsonlite::fromJSON(rawToChar(response_org$content))

# Extract the tickets from the JSON response

organizations <- response_json$organizations

# Process the tickets

for (i in 1:nrow(organizations)) {

# Access the desired ticket information

url_ticket <- paste0("https://", subdomain, ".zendesk.com/api/v2/search.json?query=type%3Aticket+organization%3A", organizations$id[i], "&created_after%3E2022-04-01")

response_ticket <- GET(url_ticket, add_headers(headers))

# Check if the API call was successful

if (response_ticket$status_code == 200) {

# Extract the response content as JSON

response_json2 <- jsonlite::fromJSON(rawToChar(response_ticket$content))

# Extract the tickets from the JSON response

tickets <- response_json2$results

# Process the tickets

for (j in 1:nrow(tickets)) {

# Access the desired ticket information

ticket_id <- tickets$id[j]

ticket_subject <- tickets$subject[j]

datalist[[obj]] <- data.frame(CustomerID = organizations$id[i],

CustomerName = organizations$name[i],

TicketID = tickets$id[j],

Type = tickets$type[j],

Subject = tickets$subject[j],

CreatedAt = as.Date(tickets$created_at[j]),

UpdatedAt = as.Date(tickets$updated_at[j]))

obj <- obj+1

}

} else if (response_org$status_code == 401) {

cat("Error: Unauthorized access. Please check your API credentials.\n")

} else {

# Print the error message if the API call failed

cat("Error:", response_org$reason, "\n")

}

df <- dplyr::bind_rows(datalist)

# Create Table (Graphical) ##########################################################

g <- df %>% dplyr::arrange(desc(CreatedAt)) %>% dplyr::top_n(5) %>% dplyr::select(CustomerName, TicketID, Type, Subject, CreatedAt, UpdatedAt) %>% grid.table()

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

lbendlin — Sun, 09 Jul 2023 21:35:28 GMT

Check this first before committing any more time to that approach

Learn which R packages are supported - Power BI | Microsoft Learn

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

RAnalytics — Mon, 10 Jul 2023 05:05:17 GMT

Hi Ibendlin, thanks for the feedback. I initially made sure that the packages I was using were supported. All the ones I use are listed

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

lbendlin — Mon, 10 Jul 2023 23:36:59 GMT

Is your personal gateway up and running?

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

RAnalytics — Tue, 11 Jul 2023 02:54:22 GMT

Yes, gateway is on but it is standard mode not one in personal-mode

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

lbendlin — Tue, 11 Jul 2023 09:46:33 GMT

R and Python scripts and visuals require the use of a personal gateway. They won't work with a standard gateway.

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

RAnalytics — Thu, 13 Jul 2023 09:14:40 GMT

Ok, I'll try this after my vacation. Will give feedback at begin of August. Thank you so far

topic API Call with R works on PowerBI-Desktop but not in PowerBI-Service in Developer

API Call with R works on PowerBI-Desktop but not in PowerBI-Service

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service

Re: API Call with R works on PowerBI-Desktop but not in PowerBI-Service