如何让Power Query透视表保留预定义列并按指定顺序显示

问题需求 #

需要对表格执行透视操作，满足两个核心要求：

1. 无论ID列实际有多少个值，透视后必须显示固定的5列，缺失的列用0填充；
2. 列需严格按以下顺序排列：1 - TO START、2 - IN PROGRESS、3 - CANCELLED、4 - STANDBY、5 - FINISHED。

预期输出：

+------------+---------------+-------------+-----------+------------+
|   TO START |   IN PROGRESS |   CANCELLED |   STANDBY |   FINISHED |
|------------+---------------+-------------+-----------+------------|
|          6 |            13 |           1 |         0 |         14 |
+------------+---------------+-------------+-----------+------------+

现有代码无法满足需求，最小可复现代码如下：

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8vRTCAjydw9yDQ5W0lEyUorVQRczBou5efp5Bnu4ugAFTMECzo5+zq4+PmARA7BIiL9CcIhjUAhcD7ISQ+xKkIzFZrcFuiJzpdhYAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, HOW_MANY = _t]
  ),
  Types = Table.TransformColumnTypes(Source, {{"ID", type text}, {"HOW_MANY", Int64.Type}}),
  Pivot = Table.Pivot(Types, List.Distinct(Types[ID]), "ID", "HOW_MANY", List.Sum)
in
  Pivot

修正后的实现代码 #

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8vRTCAjydw9yDQ5W0lEyUorVQRczBou5efp5Bnu4ugAFTMECzo5+zq4+PmARA7BIiL9CcIhjUAhcD7ISQ+xKkIzFZrcFuiJzpdhYAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, HOW_MANY = _t]
  ),
  Types = Table.TransformColumnTypes(Source, {{"ID", type text}, {"HOW_MANY", Int64.Type}}),
  // 定义必须显示的5个固定状态列
  TargetIDs = {"1 - TO START", "2 - IN PROGRESS", "3 - CANCELLED", "4 - STANDBY", "5 - FINISHED"},
  // 给缺失的状态添加HOW_MANY=0的记录
  AddMissingIDs = Table.FromRecords(
    List.Combine({
      Table.ToRecords(Types),
      List.Transform(List.Difference(TargetIDs, List.Distinct(Types[ID])), (id) => [ID = id, HOW_MANY = 0])
    })
  ),
  // 按固定顺序透视表格
  Pivot = Table.Pivot(AddMissingIDs, TargetIDs, "ID", "HOW_MANY", List.Sum),
  // 去掉列名里的序号前缀，改成纯状态名称
  RenameColumns = Table.RenameColumns(Pivot, List.Transform(TargetIDs, (col) => {col, Text.AfterDelimiter(col, " - ")})),
  // 强制列顺序与预期一致
  ReorderColumns = Table.ReorderColumns(RenameColumns, {"TO START", "IN PROGRESS", "CANCELLED", "STANDBY", "FINISHED"})
in
  ReorderColumns

核心修改点说明 #

• 固定目标列清单：提前把需要的5个状态列写死在TargetIDs数组里，确保透视时不会因为原数据缺失某些状态而丢列。
• 补全缺失数据：对比原数据的ID和目标列，把没出现的状态补上一行HOW_MANY=0的记录，这样透视时缺失列就会显示0。
• 按固定顺序透视：透视时直接用TargetIDs作为列名列表，而非原数据的动态去重ID，保证列顺序完全符合要求。
• 调整列名格式：用Text.AfterDelimiter去掉列名里的序号前缀，匹配预期的列名样式。
• 强制列顺序：最后手动调用Table.ReorderColumns再次确认列顺序，彻底避免意外排序问题。

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