Data explorer - Power BI

dev
1

Hello,
I’m using the following code to call data from Data Explorer to PBI through API

(queryID) => let
resultCount = 10000,
otherNameForPage = 0,

    GetPage = (otherNameForPage) =>
        let
            content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
            RawData = Json.Document(Web.Contents(
                "https://forum.xxxxxx.com/admin/plugins/explorer/queries",
                [RelativePath=Number.ToText(queryID) & "/run",
                    Query=
                    [
                        params="{""page"":""" & Number.ToText(otherNameForPage) & """}"
                    ],

                    Headers = [ 
                    #"api-username"="xxxxxxxx",
                    #"api-key"="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
                    #"Content-Type" = "application/x-www-form-urlencoded"],
                    Content = Text.ToBinary(content1)
                ]
                
            ) ),
            resultCount = RawData[result_count]
        in
            if RawData[result_count] = 0 then null else RawData,
        Pages = List.Generate(
            () => [i = 0, RawData = GetPage(i)],
            each [RawData] <> null,
            each [i=[i]+1, RawData = GetPage(i)],
            each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
        Output = Table.Combine(Pages)

in
Output

I was able to load data from query that contain this code :

–[params]
– integer :page = 0

select users.id, users.username, user_custom_fields.value from users, user_custom_fields
where users.id = user_custom_fields.user_id and user_custom_fields.name =‘user_field_1’

OFFSET :page * 10000
LIMIT 10000

but when I call another report that contains the following query

SELECT
  user_id,
  COUNT(*) AS visits
FROM user_visits
WHERE visited_at > CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC

it returns 422 : Unprocessable Entity
( it take much time before returning this error)

Is it possible to fix it?

2

Does that query runs on the Data Explorer UI? If it works there, but not on your PowerBI integration the problem lies elsewhere from Discourse.

1 Like
3

@Falco, that right, the report works instantly in Data Explorer UI

4

Was looking for the source for the error, I found this description :
The HyperText Transfer Protocol (HTTP) **422 Unprocessable Entity** response status code indicates that the server understands the content type of the request entity, and the syntax of the request entity is correct, but it was unable to process the contained instructions.
it seems to come from the server ? no ?