Discourse Data Explorer Power BI integration

Community wiki Administrators
,
11

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.

3 Likes