Thanks a lot, Jon! I managed to build a Speckle Comments Extractor for Power BI. I’m sharing the script in case it’s useful to someone else. Requirements: Power BI Desktop (Data Extensions enabled), Speckle Desktop Service, and read access to the target project. Just replace projectId with your actual ID.
SPECKLE COMMENTS EXTRACTOR FOR POWER BI
Requirements: Power BI Desktop (Data Extensions), Speckle Desktop Service and read access to the target project.
Configure: projectId with your actual ID.
let
// --- Basic configuration ---
baseUrl = "https://app.speckle.systems",
projectId = "{PROJECT_ID}",
// --- GraphQL query ---
threadsQuery = "
query($projectId: String!, $filter: ProjectCommentsFilter) {
project(id: $projectId) {
commentThreads(filter: $filter) {
totalCount
items {
id
rawText
createdAt
updatedAt
archived
authorId
author { name id }
replies {
totalCount
items {
id
rawText
createdAt
authorId
author { name id }
}
}
viewerState
}
}
}
}
",
// --- Query variables ---
vars = [
projectId = projectId,
filter = [ includeArchived = true ]
],
// --- Step 1: Fetch data from API ---
response = Speckle.Api.Fetch(baseUrl, threadsQuery, vars),
threads = response[project][commentThreads][items],
threadsTable = Table.FromList(threads, Splitter.SplitByNothing()),
// --- Step 2: Expand thread structure ---
expandedThreads = Table.ExpandRecordColumn(
threadsTable,
"Column1",
{"id", "rawText", "createdAt", "updatedAt", "archived", "authorId", "author", "replies", "viewerState"},
{"ThreadId", "RawText", "CreatedAt", "UpdatedAt", "Archived", "AuthorId", "Author", "Replies", "ViewerState"}
),
expandedAuthor = Table.ExpandRecordColumn(
expandedThreads,
"Author",
{"name", "id"},
{"AuthorName", "AuthorIdDetail"}
),
// --- Step 3: Time-based metrics ---
withTimeCalculations = Table.AddColumn(
expandedAuthor,
"TimeMetrics",
each
let
createdDateTime = DateTime.FromText([CreatedAt]),
now = DateTime.LocalNow(),
dDays = Number.RoundDown(Duration.TotalDays(now - createdDateTime)),
dHours = Number.RoundDown(Duration.TotalHours(now - createdDateTime)),
dMinutes = Number.RoundDown(Duration.TotalMinutes(now - createdDateTime)),
timeAgoText =
if dDays >= 1 then Text.From(dDays) & " days ago"
else if dHours >= 1 then Text.From(dHours) & " hours ago"
else if dMinutes >= 1 then Text.From(dMinutes) & " minutes ago"
else "Just now",
activityLevel =
if dDays = 0 then "Today"
else if dDays <= 7 then "Recent"
else if dDays <= 30 then "Active"
else "Old"
in
[
DaysSinceCreated = dDays,
HoursSinceCreated = dHours,
MinutesSinceCreated = dMinutes,
TimeAgoText = timeAgoText,
ActivityLevel = activityLevel
]
),
expandedTimeMetrics = Table.ExpandRecordColumn(
withTimeCalculations,
"TimeMetrics",
{"DaysSinceCreated", "HoursSinceCreated", "MinutesSinceCreated", "TimeAgoText", "ActivityLevel"},
{"DaysSinceCreated", "HoursSinceCreated", "MinutesSinceCreated", "TimeAgoText", "ActivityLevel"}
),
// --- Step 4: Discussion metrics ---
withMetrics = Table.AddColumn(
expandedTimeMetrics,
"CommentMetrics",
each
let
repliesRecord = [Replies],
totalReplies = if repliesRecord <> null then Record.Field(repliesRecord, "totalCount") else 0,
repliesItems = if repliesRecord <> null then Record.Field(repliesRecord, "items") else {},
threadCreatedAt = DateTime.FromText([CreatedAt]),
threadUpdatedAt = if [UpdatedAt] <> null then DateTime.FromText([UpdatedAt]) else threadCreatedAt,
replyDates =
if List.Count(repliesItems) > 0
then List.Transform(repliesItems, each DateTime.FromText(Record.Field(_, "createdAt")))
else {},
firstCommentDate =
if List.Count(replyDates) > 0
then List.Min({threadCreatedAt} & replyDates)
else threadCreatedAt,
lastCommentDate =
if List.Count(replyDates) > 0
then List.Max({threadCreatedAt} & replyDates)
else threadUpdatedAt,
status = if [Archived] = true then "Resolved" else "Open"
in
[
ResponseCount = totalReplies,
FirstCommentDate = firstCommentDate,
LastCommentDate = lastCommentDate,
Status = status
]
),
expandedMetrics = Table.ExpandRecordColumn(
withMetrics,
"CommentMetrics",
{"ResponseCount", "FirstCommentDate", "LastCommentDate", "Status"},
{"ResponseCount", "FirstCommentDate", "LastCommentDate", "Status"}
),
// --- Step 5: Display-friendly title ---
withTitle = Table.AddColumn(
expandedMetrics,
"Title",
each
if [RawText] <> null and Text.Length([RawText]) > 0 then
if Text.Length([RawText]) > 50
then Text.Start([RawText], 47) & "..."
else [RawText]
else
"Comment " & Text.End([ThreadId], 8)
),
// --- Step 6: Data types and cleanup ---
finalTable =
Table.TransformColumnTypes(
Table.RemoveColumns(withTitle, {"Replies", "AuthorIdDetail", "ViewerState"}),
{
{"CreatedAt", type datetime},
{"UpdatedAt", type datetime},
{"FirstCommentDate", type datetime},
{"LastCommentDate", type datetime},
{"ResponseCount", Int64.Type},
{"Archived", type logical},
{"DaysSinceCreated", Int64.Type},
{"HoursSinceCreated", Int64.Type},
{"MinutesSinceCreated", Int64.Type}
}
)
in
finalTable
I’ll be glad if this helps others in the community. Feedback or improvements are welcome!
Speckle.GetDiscussions will be really useful and definitely simplify the process.