使用Power Query或DAX实现动态列数的长表转宽表

问题说明 #

需要将父子结构的长格式表格转为宽格式，但每个ParentID对应的子记录数量不固定，无法预先确定要生成的子项列数，需动态适配最大子项数量。

原始长表 #

期望宽表结果 #

Power Query 动态转宽步骤 #

1. 导入数据到Power Query：在Excel中点击「数据」选项卡→「自表格/区域」，选中原始数据导入。
2. 为每个ParentID的子项添加序号：
   点击「添加列」→「自定义列」，输入以下公式（替换源为当前查询的上一步名称），将新列命名为ChildIndex：

   Table.RowNumber(Table.SelectRows(源, (r) => r[ParentID] = [ParentID]))
   

3. 透视列生成动态宽表：
   选中ChildIndex列，点击「转换」→「透视列」：
   • 值列：按住Ctrl选中所有子项列（Child ID、Child Name、Child Rank、Child Hobby）
   • 高级选项：选择「不要聚合」
     此时Power Query会自动识别所有ParentID的最大子项数，生成对应数量的列，不足的位置填充空值。
4. 整理列名与顺序：自动生成的列名类似Child ID_1，可批量重命名为ChildID.1格式，调整列顺序后加载回Excel。

DAX 实现方案 #

DAX无法直接在模型中生成动态列（列数固定），但可通过以下两种方式实现需求：

方式1：生成拼接式宽表（适合查看） #

先创建计算列，把每个ParentID的子项信息拼接成字符串：

子项信息 = 
VAR 当前父项子记录 = FILTER('原始表', '原始表'[ParentID] = EARLIER('原始表'[ParentID]))
RETURN
CONCATENATEX(
    当前父项子记录,
    "子ID:" & [Child ID] & "，姓名:" & [Child Name] & "，排名:" & [Child Rank] & "，爱好:" & [Child Hobby],
    " | "
)

再用SUMMARIZE生成唯一父项的宽表：

宽表 = 
SUMMARIZE(
    '原始表',
    '原始表'[ParentID],
    '原始表'[Parent Age],
    "子项汇总", [子项信息]
)

方式2：动态度量值+矩阵展示（适合Power BI报表） #

如果在Power BI中使用，可通过动态度量值结合矩阵实现动态列展示：

1. 生成子项索引表：

   子项索引表 = 
   VAR 最大子项数 = MAXX(SUMMARIZE('原始表', '原始表'[ParentID], "子项数", COUNTROWS('原始表')), [子项数])
   RETURN GENERATESERIES(1, 最大子项数, 1)
   

2. 创建动态度量值：以获取对应索引的子ID为例，同理创建姓名、排名、爱好的度量值：

   动态子ID = 
   VAR 当前索引 = SELECTEDVALUE('子项索引表'[Value])
   VAR 当前父ID = SELECTEDVALUE('原始表'[ParentID])
   VAR 对应子记录 = CALCULATE(
       MAX('原始表'[Child ID]),
       FILTER('原始表', '原始表'[ParentID] = 当前父ID && Table.RowNumber(FILTER('原始表', '原始表'[ParentID] = 当前父ID)) = 当前索引)
   )
   RETURN 对应子记录
   

3. 矩阵展示：矩阵的行字段选ParentID和Parent Age，列字段选子项索引表[Value]，值字段添加四个动态度量值，即可自动适配所有子项列。

内容的提问来源于Stack Exchange，提问作者yzhao