Parsing Copilot Studio Conversation Transcripts in Power Apps
Ever stared at a Copilot Studio transcript JSON and wondered how to turn that nested mess into a clean, readable table?
The raw transcript comes as a single JSON string with dozens of activities: traces, events, debug plans, tool calls, and somewhere buried in there, the actual user prompts and bot responses. Most of it is plumbing. What you really care about are the messages where role = 1 (user) and role = 0 (bot), paired together by the replyToId field.
The trick in Power Fx is ParseJSON combined with a Clear + Collect pattern. ForAll iterates over each transcript, filters activities down to type = "message", then pairs each user prompt with its matching bot response using the replyToId. Timestamps come through as Unix epoch seconds, so a quick DateAdd against DateTime(1970,1,1,0,0,0) with TimeUnit.Seconds converts them to real datetimes.
Here's the full formula I dropped into App.OnStart:
Clear(colChatTurns);
ForAll(
ConversationTranscripts As Transcript,
With(
{ parsed: ParseJSON(Transcript.Content) },
With(
{
allMessages: Filter(
Table(parsed.activities),
Text(ThisRecord.Value.type) = "message"
)
},
ForAll(
Filter(allMessages, Text(ThisRecord.Value.from.role) = "1") As UserMsg,
Collect(
colChatTurns,
{
ReplyToId: Text(UserMsg.Value.id),
UserPrompt: Text(UserMsg.Value.text),
PromptTimestamp: DateAdd(
DateTime(1970,1,1,0,0,0),
Value(UserMsg.Value.timestamp),
TimeUnit.Seconds
),
BotResponse: Text(
First(
Filter(
allMessages,
Text(ThisRecord.Value.from.role) = "0"
And Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
)
).Value.text
),
ResponseTimestamp: DateAdd(
DateTime(1970,1,1,0,0,0),
Value(
First(
Filter(
allMessages,
Text(ThisRecord.Value.from.role) = "0"
And Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
)
).Value.timestamp
),
TimeUnit.Seconds
)
}
)
)
)
)
)
A few gotchas worth knowing. ParseJSON returns untyped objects, so every field needs explicit coercion with Text() or Value(). The role field can behave as either a number or a string depending on the source, so comparing with "1" and "0" as strings is safer than numeric comparisons. Ungroup sounds like the right tool for flattening nested results, but it chokes on string identifiers — Clear + Collect inside a ForAll loop is cleaner and easier to debug. And always check View → Collections to see if your data is actually landing where you think it is.
Once the collection is populated, binding it to a Data Table gives you an instant conversation viewer. Load it on App.OnStart for speed, or on Screen.OnVisible if you need fresh data every visit. Skip the intermediate gallery and point straight at your data source to keep things lean.
From messy telemetry JSON to a working chat review dashboard.
Tags: PowerApps, PowerFx, CopilotStudio, LowCode, MicrosoftPowerPlatform, JSON, ParseJSON, Dataverse, SharePoint, ConversationAnalytics, ChatbotAnalytics, AIAgents, PowerAppsTutorial, EnterpriseIT, SolutionArchitecture
