Copilot Studio stores every bot conversation as a JSON payload inside the ConversationTranscripts Dataverse table. That raw JSON contains every user message, every bot reply, timestamps, session metadata, and even GPT trace data — but there is no built-in UI to browse it meaningfully. In this guide, we build a fully functional 3-screen Power Apps canvas app that lets you filter sessions, browse turns, and read full conversation detail — all driven directly from Dataverse with no intermediate API.
📋 Table of Contents
- What Is the ConversationTranscripts Table?
- App Architecture Overview
- Collections and Global Variables
- Screen 1 — Session List
- Screen 2 — Conversation Detail
- Screen 3 — Turn Detail
- Parsing the JSON Activities Array
- Matching User Prompts to Bot Responses
- Common Issues and Fixes
- Full Formula Reference
1. What Is the ConversationTranscripts Table?
Every time a user has a conversation with a Copilot Studio bot, a new record is created in the ConversationTranscripts Dataverse table. Each record contains a Content column that holds the full conversation as a JSON string. The JSON has an activities array — each activity is either a message, event, or trace.
Key columns used in this app:
| Column | Type | Purpose |
|---|---|---|
| Name | Text | Unique record identifier (SessionId) |
| Content | Text (JSON) | Full activities array for the session |
| ConversationStartTime | DateTime | When the session started |
| bot_conversationtranscript | Lookup | The bot that handled the session |
| conversationtranscript | Text | Session GUID used as display identifier |
A Turn = one complete user message + bot reply. A session with TurnCount = 0 means the user opened the bot but sent no message — nothing worth displaying.
2. App Architecture Overview
The app uses a 3-screen navigation model — session list → conversation detail → turn detail. Navigation flows forward with ScreenTransition.Cover and back with ScreenTransition.UnCover, giving a native app feel.
| Screen | Name | Shows |
|---|---|---|
| Screen 1 | scrSessionList | All sessions, filtered by agent/date/search |
| Screen 2 | scrConversationDetail | All turns (Q&A pairs) for the selected session |
| Screen 3 | scrTurnDetail | Full text of selected user prompt + bot response |
3. Collections and Global Variables
| Name | Type | Purpose |
|---|---|---|
| colSessions | Collection | Session list built from filtered Dataverse records |
| colCurrentConversation | Collection | Parsed turns for the selected session |
| gSelectedSession | Record variable | Session clicked on Screen 1 |
| gSelectedTurn | Record variable | Turn clicked on Screen 2 |
colSessions schema:
{
SessionId: Text,
AgentName: Text,
ConversationStartTime: DateTime,
Session: Text,
FirstPrompt: Text,
TurnCount: Number
}colCurrentConversation schema:
{
UserPrompt: Text,
PromptTimestamp: DateTime,
BotResponse: Text,
ResponseTimestamp: DateTime
}4. Screen 1 — Session List
Screen 1 has three filter controls (agent dropdown, start date, end date), a search box, and a Load Sessions button that populates colSessions.
Figure 1 — Screen 1: Session list with agent filter, date range, and keyword search
Agent Dropdown Items:
=Table(
{Value: "All"},
Distinct(ConversationTranscripts, 'bot_conversationtranscript'.Name)
)btnLoad OnSelect:
=Clear(colSessions);
ForAll(
Filter(
ConversationTranscripts,
(ddAgent.Selected.Value = "All" ||
'bot_conversationtranscript'.Name = ddAgent.Selected.Value) &&
ConversationStartTime >= dpStart.SelectedDate &&
ConversationStartTime <= DateAdd(dpEnd.SelectedDate, 1, TimeUnit.Days)
) As T,
With({parsed: ParseJSON(T.Content)},
With({firstUserMsg: First(Filter(Table(parsed.activities),
Text(ThisRecord.Value.type) = "message" &&
Text(ThisRecord.Value.from.role) = "1"))},
Collect(colSessions, {
SessionId: T.Name,
AgentName: T.'bot_conversationtranscript'.Name,
ConversationStartTime: T.ConversationStartTime,
Session: T.conversationtranscript,
FirstPrompt: Text(firstUserMsg.Value.text),
TurnCount: CountRows(Filter(Table(parsed.activities),
Text(ThisRecord.Value.type) = "message" &&
Text(ThisRecord.Value.from.role) = "1"))
})
)
)
)galSessions Items:
=SortByColumns(
Filter(
colSessions,
TurnCount > 0 &&
(IsBlank(txtSearch.Text) || txtSearch.Text in FirstPrompt)
),
"ConversationStartTime",
SortOrder.Descending
)💡 Why TurnCount > 0? Sessions where the user opened the bot but typed nothing still create a Dataverse record with zero user message activities. Filtering these out keeps the list meaningful.
galSessions OnSelect:
=Set(gSelectedSession, ThisItem);
If(
ThisItem.TurnCount > 0,
Clear(colCurrentConversation);
With({parsed: ParseJSON(LookUp(ConversationTranscripts,
Name = gSelectedSession.SessionId).Content)},
ForAll(
Filter(Table(parsed.activities),
Text(ThisRecord.Value.type) = "message" &&
Text(ThisRecord.Value.from.role) = "1") As UserMsg,
Collect(colCurrentConversation, {
UserPrompt: Text(UserMsg.Value.text),
PromptTimestamp: DateAdd(DateTime(1970,1,1,0,0,0),
Value(UserMsg.Value.timestamp), TimeUnit.Seconds),
BotResponse: Text(First(Filter(Table(parsed.activities),
Text(ThisRecord.Value.type) = "message" &&
Text(ThisRecord.Value.from.role) = "0" &&
Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
)).Value.text),
ResponseTimestamp: DateAdd(DateTime(1970,1,1,0,0,0),
Value(First(Filter(Table(parsed.activities),
Text(ThisRecord.Value.type) = "message" &&
Text(ThisRecord.Value.from.role) = "0" &&
Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
)).Value.timestamp), TimeUnit.Seconds)
})
)
);
Navigate(scrConversationDetail, ScreenTransition.Cover)
)5. Screen 2 — Conversation Detail
Screen 2 shows all turns for the selected session sorted by timestamp, with a search box to find specific turns by keyword.
Figure 2 — Screen 2: All Q&A turns for the selected session, with turn-level search
galConversation Items:
=SortByColumns(
Filter(
colCurrentConversation,
!IsBlank(ThisRecord.UserPrompt) &&
!IsBlank(ThisRecord.BotResponse) &&
(IsBlank(txtSearchTurn.Text) || txtSearchTurn.Text in ThisRecord.UserPrompt)
),
"PromptTimestamp",
SortOrder.Ascending
)galConversation OnSelect:
=Set(gSelectedTurn, ThisItem);
Navigate(scrTurnDetail, ScreenTransition.Cover)
💡 Tip: The three conditions in the gallery Items formula work together — blank turns are excluded, and search is applied only when the user types something. When the search box is empty, IsBlank(txtSearchTurn.Text) returns true and all valid turns are shown.
6. Screen 3 — Turn Detail
Screen 3 displays the full text of a single selected turn — user prompt and bot response — using read-only multiline text inputs. This solves the gallery truncation problem for long bot responses.
Figure 3 — Screen 3: Full user prompt and bot response for the selected turn
| Control | Property | Value |
|---|---|---|
| txtUserPrompt | Default | =gSelectedTurn.UserPrompt |
| txtUserPrompt | DisplayMode | =DisplayMode.View |
| txtUserPrompt | Mode | =TextMode.MultiLine |
| txtBotResponse | Default | =gSelectedTurn.BotResponse |
| txtBotResponse | DisplayMode | =DisplayMode.View |
| txtBotResponse | Mode | =TextMode.MultiLine |
| lblPromptTimestamp | Text | ="Prompt: " & Text(gSelectedTurn.PromptTimestamp, "dd mmm yyyy hh:mm AM/PM") |
| lblResponseTimestamp | Text | ="Response: " & Text(gSelectedTurn.ResponseTimestamp, "dd mmm yyyy hh:mm AM/PM") |
| lblAgentName | Text | ="Agent: " & gSelectedSession.AgentName |
| btnBack3 | OnSelect | =Navigate(scrConversationDetail, ScreenTransition.UnCover) |
7. Parsing the JSON Activities Array
The Content column is a raw JSON string. Power Fx's ParseJSON() function converts it to an untyped object. Wrapping it in Table() converts the activities array into a Power Apps table with a Value column — each row representing one activity.
With({parsed: ParseJSON(Transcript.Content)},
Table(parsed.activities)
)The from.role field identifies who sent each message:
| role value | Meaning |
|---|---|
| 1 | User message |
| 0 | Bot message |
⚠️ Important: All values from ParseJSON are untyped. Always wrap field access in Text() before comparing:
// Correct
Text(ThisRecord.Value.from.role) = "1"
// Incorrect — silently fails
ThisRecord.Value.from.role = 18. Matching User Prompts to Bot Responses
Each bot response activity has a replyToId field containing the id of the user message it responds to. This is the key to correctly pairing prompts and responses.
BotResponse: Text(
First(
Filter(
Table(parsed.activities),
Text(ThisRecord.Value.type) = "message" &&
Text(ThisRecord.Value.from.role) = "0" &&
Text(ThisRecord.Value.replyToId) = Text(UserMsg.Value.id)
)
).Value.text
)
⚠️ Critical scoping rule: Always reference parsed.activities directly inside the bot response Filter — never use an intermediate variable like allMessages. Power Apps ForAll closures can cause stale variable references across transcript records, resulting in bot responses from a different session appearing for the wrong user prompt.
9. Common Issues and Fixes
| Issue | Cause | Fix |
|---|---|---|
| Bot response shows text from a different session | Intermediate variable leaks across ForAll iterations | Reference Table(parsed.activities) directly in all filters |
| TurnCount = 0 sessions appear in list | Gallery Items formula missing turn filter | Add TurnCount > 0 to gallery Items Filter |
| Timestamps show 1970 or wrong date | JSON timestamp is Unix epoch in seconds | Use DateAdd(DateTime(1970,1,1,0,0,0), Value(...), TimeUnit.Seconds) |
| ParseJSON comparison returns no rows | Comparing untyped value without Text() wrapper | Always use Text(ThisRecord.Value.field) = "value" |
| Empty BotResponse for some turns | Session abandoned before bot replied; no matching replyToId | Expected behavior — filter with !IsBlank(ThisRecord.BotResponse) |
| Gallery shows all sessions despite filter | Delegation limit exceeded on Dataverse | Narrow the date range to stay within delegation limits |
10. Full Formula Reference
Screen 1 — Gallery Items:
=SortByColumns(
Filter(
colSessions,
TurnCount > 0 &&
(IsBlank(txtSearch.Text) || txtSearch.Text in FirstPrompt)
),
"ConversationStartTime",
SortOrder.Descending
)Screen 2 — Gallery Items:
=SortByColumns(
Filter(
colCurrentConversation,
!IsBlank(ThisRecord.UserPrompt) &&
!IsBlank(ThisRecord.BotResponse) &&
(IsBlank(txtSearchTurn.Text) || txtSearchTurn.Text in ThisRecord.UserPrompt)
),
"PromptTimestamp",
SortOrder.Ascending
)Epoch to DateTime conversion:
=DateAdd(
DateTime(1970, 1, 1, 0, 0, 0),
Value(activity.timestamp),
TimeUnit.Seconds
)
✅ Summary: The ConversationTranscripts Dataverse table is a rich source of Copilot Studio conversation data, but it requires JSON parsing to be useful. By combining Dataverse delegation for filtering, ParseJSON + Table() for activity parsing, and replyToId matching for turn pairing, you can build a production-ready 3-screen conversation viewer entirely in Power Apps with no code, no flows, and no external APIs. The scoping rule — always reference parsed.activities directly rather than through intermediate variables — is the most important detail to get right when working across multiple transcript records.
Labels: Copilot Studio, Dataverse, Power Apps, Power Fx, Power Platform, Canvas App
No comments:
Post a Comment