[Tutorial] Discourse Data Explorer Power BI integration

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:

  1. The api-username and api-key (see function above for the placeholders):

                         Headers = [ 
                         #"api-username"="yourAPIUsername",
                         #"api-key"="yourAPIKey",
    
  2. 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:
    image

    This should look like so in Power BI after you click on the function:
    image

Sample output

This is the output of the sample query:

image

That’s it! You can now shape your data however you want.

7 Likes

You had too much free time on your hands. :laughing:

Thank you tho :slight_smile: very useful.

2 Likes

For others like me who see Power BI and are confused.

4 Likes

Dzięki @loginerror for providing that! I’ll be trying to achieve the same thing in Tableau. Not sure if it has similar advanced editor to what Power BI offers but will see

1 Like

For others like me who do not know about Tableau.

Get a full picture of your business, inside and out.
Connect all of your data with critical external sources like public health status, economic indicators, and government policy.


From Wikipedia

Tableau products query relational databases, online analytical processing cubes, cloud databases, and spreadsheets to generate graph-type data visualizations. The products can also extract, store, and retrieve data from an in-memory data engine.

hey @loginerror thanks for providing this!

I’m just getting into PowerBI and would be a fun project to work on a Discourse dashboard, and thanks to you I may be able to achieve the initial steps and get faster to the fun part.

But for this I’d like to ask a question…
I’ve tried to run the suggested query using a newly generated API key (to my user name) but can’t make it work for some reason.

I see that there is something going on Discourse - it indicates that the API key has been used but the PowerBI endlessly runs the query and doesn’t provide any data.

I’m not proficient in queries at all, so I may be missing something obvious… Could you please help?

It should simply work when plugged in.

Have you tried running it with the sample query that only returns one value?

--[params]
-- integer :page = 0

SELECT count(*) from badges

OFFSET :page * 1000
LIMIT 1000

Otherwise, if you have a bit more pages (and we are limited to 1000 records per page), it might take longer to load all the data.

I’ve just tested the code by making a quick Power BI project with the code from the original post and it runs just fine (it seems to run just fine on either a System API key or a user one with admin permissions).

3 Likes

Oh, I didn’t realize you answered so fast - thanks!
I may come as a complete noob, and probably that’s because I am. But where do I use this piece of code? Sorry, I’m really new to powerbi…

1 Like

This is a sample Data Explorer query you should save in your Data Explorer plugin. It is designed to only return 1 record, which should test out the function without forcing it to download a lot of data.

Afterwards, grab its ID from the URL and plug it in Power BI function created from the code from the first post:

/admin/plugins/explorer?id=260
1 Like

Ah, I see now! Thanks so much. I was just looking at the things from Power BI point of view… how silly :slight_smile:

I’ll see how it goes from here.

Thanks again, great stuff.

1 Like

Quick update, in case someone wants to improve the script from the first post.

It is actually possible to pull all data in one go, seemingly independent of how much data there is :sweat_smile:
(I stress-tested this on a sizeable posts table with id and raw columns and it managed!)

Sample query:

SELECT      'id'
            || string_agg(E'\n' || case when posts.id::text is null then '-1' else posts.id::text end
            ,''
            ORDER BY posts.id asc) as value
FROM        posts

When you run it, it will print your almost-csv file as one value, so also on one page. You would then need to trim the value" and another " at the end and it becomes a useful CSV:
image

You can naturally add more data to it by adding a new column for CSV file and a new data for that column:

SELECT      'id,newcolumn'
            || string_agg(E'\n' || case when posts.id::text is null then '-1' else posts.id::text end
            || string_agg(E'\n' || case when posts.newcolumn::text is null then '-1' else posts.newcolumn::text end
            ,''
            ORDER BY posts.id asc) as value
FROM        posts

When updated to support such a query, Power BI wouldn’t need to traverse across multiple pages, resulting in faster data retrieval.

2 Likes