json - 使用 PowerQuery 从列表中的 JSON 记录中提取逗号分隔值

Question

作为我为我的团队创建的工具的一部分，我通过 PowerQuery 连接到内部网络服务。

Web 服务返回嵌套的 JSON，我无法将 JSON 数据解析为我要查找的格式。具体来说，我在将列中的记录内容提取到逗号分隔列表时遇到问题。

数据

如您所见，数据包含与特定“种族”(race_id) 相关的详细信息。我想关注的是 driver_codes 中的信息，它是一个记录列表。记录的数量从 0 到 4 不等，每条记录的结构为 id: 50000(50000 可以是任何 5 位数字)。所以它可能是:

编号:10000
编号:20000
编号:30000

根据要求，原始 JSON 的示例片段:

<race>
    <race_id>ABC123445</race_id>
    <begin_time>2018-03-23T00:00:00Z</begin_time>
    <vehicle_id>gokart_11</vehicle_id>
    <driver_code>
        <id>90200</id>
    </driver_code> 
    <driver_code>
        <id>90500</id>
    </driver_code>
</race>

我希望它的结构如下:

10000,20000,30000

问题

当我在带有列表的列上选择“提取值”时，我收到以下消息:

Expression.Error: We cannot convert a value of type Record to type Text.

如果我改为选择“扩展到新行”，则会为每个唯一的驱动程序代码创建重复的行。我现在每个唯一的 race_id 有几行，但我想要的是每个唯一的 race_id 一行和驱动程序代码的串联列表。

我尝试过的

我曾尝试按 race_id 对数据进行分组，但分组数据时允许的操作不包括连接行。

我也尝试过取消透视该列，但这给我留下了同样的问题:我仍然得到多行。

我已经用谷歌搜索(和 Stack Overflowed)这个问题，但运气不佳。不过，这可能是我使用了错误的关键字，所以如果存在重复，我深表歉意。

更新:我根据目前的答案尝试了什么

我尝试了 Alexis Olson 的优秀且非常详细的方法，但我最终遇到以下错误:

Expression.Error: We cannot convert the value "id" to type Number. Details:

Value=id Type=Type

错误来自于使用以下任何一行 M 代码(一行带有 List.Transform，另一行没有):

= Table.Group(#"Renamed Columns", {"race_id", "begin_time", "vehicle_id"},
 {{"DriverCodes", each Text.Combine([driver_code][id], ","), type text}})
= Table.Group(#"Renamed Columns", {"race_id", "begin_time", "vehicle_id"},
 {{"DriverCodes", each Text.Combine(List.Transform([driver_code][id], each Number.ToText(_)), ","), type text}})

注意:如果我不写 [driver_code][id] 而只写 [id] 那么我会收到另一个错误，指出 [id] 不存在。

Answer 1

这是与您提供的 XML 示例等效的 JSON:

{"race": {
    "race_id": "ABC123445",
    "begin_time": "2018-03-23T00:00:00Z",
    "vehicle_id": "gokart_11",
    "driver_code": [
      { "id": "90200" },
      { "id": "90500" }
    ]}}

如果将其加载到查询编辑器中，将其转换为表格，并展开值记录，您将拥有一个如下所示的表格:

此时，选择 Expand to New Rows，然后展开 id 列，使您的表格如下所示:

此时，您可以应用@mccard 建议的技巧。按第一列分组，然后使用最大值汇总最后一列。

最后一步生成的 M 代码如下:

= Table.Group(#"Expanded driver_code1",
              {"Name", "race_id", "begin_time", "vehicle_id"},
              {{"id", each List.Max([id]), type text}})

除此之外，您还想将 List.Max 替换为 Text.Combine，如下所示:

= Table.Group(#"Changed Type",
              {"Name", "race_id", "begin_time", "vehicle_id"},
              {{"id", each Text.Combine([id], ","), type text}})

请注意，如果您的 id 列不是文本格式，则会引发错误。要解决此问题，请在使用 Transform Tab > Data Type: Text 对行进行分组之前插入一个步骤以转换类型。另一种选择是在 Text.Combine 中使用 List.Transform，如下所示:

Text.Combine(List.Transform([id], each Number.ToText(_)), ",")

无论哪种方式，你都应该得到这样的结果: