Discourse Data Explorer Power BI integration
I recently made a cool Power BI function which fetches Data Explorer data directly to Power BI with minimal overhead.
Thus, if you are like me, and you wanted to have the data being automatically pulled and refreshed via the Power BI app, here’s the code of the function you should put in the Advanced Editor in Power BI:
(queryID) => let
resultCount = 1000,
otherNameForPage = 0,
GetPage = (otherNameForPage) =>
let
content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
RawData = Json.Document(Web.Contents(
"https://forumURL/admin/plugins/explorer/queries",
[RelativePath=Number.ToText(queryID) & "/run",
Query=
[
params="{""page"":""" & Number.ToText(otherNameForPage) & """}"
],
Headers = [
#"api-username"="yourAPIUsername",
#"api-key"="yourAPIKey",
#"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
And here is a sample Data Explorer query. It includes pagination that is required for the function above to work properly (it is required even if your query returns only 1 page):
--[params]
-- integer :page = 0
SELECT count(*) from badges
OFFSET :page * 1000
LIMIT 1000
(you can easily adapt your existing queries by including first and last 2 lines of the above example)
This function will automatically continue to iterate over the pages until no more results are found.
How to get it to work?
After you load the function to Power BI, you will need to set 2 things:
-
The api-username and api-key (see function above for the placeholders):
Headers = [ #"api-username"="yourAPIUsername", #"api-key"="yourAPIKey", -
The query ID number.
To fetch your data, all you have to provide is the ID of the query which is easy to find in the query URL:
This should look like so in Power BI after you click on the function:
Sample output
This is the output of the sample query:
That’s it! You can now shape your data however you want.
very useful.
