如何用PowerQuery将带层级的Qualtrics JSON转为Excel扁平表

一、分离insert_topic与move_topic数据 #

• 进入已展开的operations查询，添加自定义列，公式：if [type] = "insert_topic" then "插入主题" else if [type] = "move_topic" then "移动主题" else "其他"，快速区分两类操作
• 也可直接用筛选行功能，分别筛选[type]等于insert_topic和move_topic的行，拆分出两个独立查询（优先处理insert_topic，这是主题创建的原始记录；move_topic用于调整层级关联）

二、构建三级主题层级关联 #

1. 提取主题基础信息 #

从insert_topic查询中展开payload字段，提取topicId、name（主题名称）、parentId（父主题ID），生成基础主题表：

• 添加自定义列层级，规则：若parentId为空（对应rootNodes里的根主题）则标记为1级（H1）；后续通过关联判断2级（H2）、3级（H3）

2. 应用move_topic的层级调整 #

move_topic的payload包含topicId和newParentId，需将这些层级更新同步到基础主题表：

• 将move_topic查询与基础主题表按topicId合并，用newParentId替换原parentId（若存在多条同主题的move记录，先按时间戳排序，保留最新的一条更新）

3. 递归关联生成三级列 #

通过PowerQuery的合并查询功能，逐级关联父主题：

• 生成H2列：将基础主题表（子表）与自身（父表）按parentId = topicId合并，提取父表的name作为H2列（针对层级为2的主题）
• 生成H3列：将已含H2的表再次与基础主题表合并，用子表的parentId匹配H2主题的topicId，提取父表的name作为H3列（针对层级为3的主题）
• 最终整理成包含H1、H2、H3的扁平表，确保每一行对应最末级主题，上级主题自动填充

三、适配树状图（Treemap）的表结构调整 #

• 确保H1、H2、H3列层级连续，空值用上级主题名称填充（比如H3为空时，复用H2的值）
• 添加数值维度列：若树状图需要展示主题权重，可统计每个主题的出现次数生成计数列

PowerQuery核心代码片段 #

let
    源 = 你的前置查询步骤,
    // 筛选并处理insert_topic记录
    筛选insert_topic = Table.SelectRows(源, each [type] = "insert_topic"),
    展开payload = Table.ExpandRecordColumn(筛选insert_topic, "payload", {"topicId", "name", "parentId"}, {"topicId", "主题名称", "parentId"}),
    添加层级标记 = Table.AddColumn(展开payload, "层级", each if [parentId] = null then 1 else 2),
    // 处理move_topic的层级更新
    筛选move_topic = Table.SelectRows(源, each [type] = "move_topic"),
    展开move_payload = Table.ExpandRecordColumn(筛选move_topic, "payload", {"topicId", "newParentId"}, {"topicId", "newParentId"}),
    // 合并并更新父ID
    合并更新表 = Table.NestedJoin(添加层级标记, {"topicId"}, 展开move_payload, {"topicId"}, "move记录", JoinKind.LeftOuter),
    展开move记录 = Table.ExpandTableColumn(合并更新表, "move记录", {"newParentId"}, {"newParentId"}),
    最终父ID = Table.AddColumn(展开move记录, "最终parentId", each if [newParentId] <> null then [newParentId] else [parentId]),
    // 关联生成H1列
    关联H1主题 = Table.NestedJoin(最终父ID, {"最终parentId"}, 最终父ID, {"topicId"}, "H1信息", JoinKind.LeftOuter),
    展开H1 = Table.ExpandTableColumn(关联H1主题, "H1信息", {"主题名称"}, {"H1"}),
    // 关联生成H2列并整理H3
    关联H2主题 = Table.NestedJoin(展开H1, {"最终parentId"}, 展开H1, {"topicId"}, "H2信息", JoinKind.LeftOuter),
    展开H2 = Table.ExpandTableColumn(关联H2主题, "H2信息", {"主题名称"}, {"H2"}),
    整理层级列 = Table.SelectColumns(展开H2, {"H1", "H2", "主题名称", "层级"}),
    重命名H3 = Table.RenameColumns(整理层级列, {{"主题名称", "H3"}}),
    填充空层级 = Table.FillDown(重命名H3, {"H1", "H2"})
in
    填充空层级

内容的提问来源于stack exchange，提问作者Rodp