Powerquery:展开其中有记录的所有列

Question

在 Microsoft Excel 2013 中使用 Power Query，我创建了一个如下所示的表格:

// To insert this in Power Query, append a '=' before the 'Table.FromRows'
Table.FromRows(
  {
    {"0", "Tom", "null", "null"},
    {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
    {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
  },
  {"ID", "Name", "Address", "Wife"}
)

现在，我想使用 name 属性扩展列 Address 和 Wife在两个记录上。手动，我会这样做:

// To insert this in Power Query, append a '=' before the 'Table.FromRows'
let
  t = Table.FromRows(
    {
      {"0", "Tom", "null", "null"},
      {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
      {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
    },
    {"ID", "Name", "Address", "Wife"}
  ),
  expAddress = Table.ExpandRecordColumn(t, "Address", {"name"}, {"Address → name"}),
  expWife = Table.ExpandRecordColumn(expAddress, "Wife", {"name"}, {"Wife → name"})
in
  expWife

背景

每当我有不同布局的数据表时，我需要重写询问。在幻想世界中，您可以展开所有包含 Records 的列他们使用特定的 key 。 理想情况下，您将拥有以下库功能:

// Returns a list with the names of the columns that match the secified type.
// Will also try to infer the type of a column if the table is untyped.
Table.ColumnsOfTypeInfer(
  table as table,
  listOfTypes as list
) as list

// Expands a column of records into columns with each of the values.
Table.ExpandRecordColumnByKey(
  table as table,
  columns as list,
  key as text,
) as table

然后，我可以调用

// To insert this in Power Query, append a '=' before the 'Table.FromRows'
let
  t = Table.FromRows(
    {
      {"0", "Tom", "null", "null"},
      {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
      {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
    },
    {"ID", "Name", "Address", "Wife"}
  ),
  recordColumns = Table.ColumnsOfTypeInfer(t, {type record}),
  expAll = Table.ExpandRecordColumnByKey(t, recordColumns, "name")
in
  expAll

问题

1. 您能否获得具有表中未指定的特定类型的列的列表，也就是推断它？
2. 你能使该记录扩展通用吗？

编辑:添加了带有两个空单元格的第 0 行。

Answer 1

(首先，感谢您提供清晰的解释以及示例数据和建议!)

1) M 代码无法进行类型推断。这种限制几乎可以被视为一种“特性”，因为如果源数据发生变化导致推断类型不同，那么它几乎肯定会破坏您的查询。

加载未类型化数据后，使用 Detect Data Type 应该很快按钮为此生成 M。或者，如果您正在从 JSON 读取数据，那么它应该已经足够类型化了。

如果您遇到这种方法不起作用的特定情况，是否想更新您的问题？ :)

2) 只要​​表的单元格值是记录，就很有可能使记录扩展成为通用的，只是有点复杂。这会找到所有行都是 null 或记录的列，并展开 name 列。

以下是您可以添加到库中的一些简单实现:

let
  t = Table.FromRows(
    {
      {"0", "Tom", null, null},
      {"1", "Bob", [ name="Berlin" , street="BarStreet" ], [ name="Mary", age=25 ]},
      {"2", "Jim", [ name="Hamburg", street="FooStreet" ], [ name="Marta", age=30 ]}
    },
    {"ID", "Name", "Address", "Wife"}
  ),

  Table.ColumnsOfAllRowType = (table as table, typ as type) as list => let
    ColumnNames = Table.ColumnNames(table),
    ColumnsOfType = List.Select(ColumnNames, (name) => 
      List.AllTrue(List.Transform(Table.Column(table, name), (cell) => Type.Is(Value.Type(cell), typ))))
  in
    ColumnsOfType,

  Table.ExpandRecordColumnByKey = (table as table, columns as list, key as text) as table  => 
    List.Accumulate(columns, table, (state, columnToExpand) => 
      Table.ExpandRecordColumn(state, columnToExpand, {key}, { columnToExpand & " → " & key })),

  recordColumns = Table.ColumnsOfAllRowType(t, type nullable record),
  expAll = Table.ExpandRecordColumnByKey(t, recordColumns, "name")
in
  expAll

---

如果一个新的库函数可以在 M 中实现，我们就不太可能将它添加到我们的标准库中，但如果您觉得缺少它，请随时在以下位置提出建议:https://ideas.powerbi.com/forums/265200-power-bi/

您可能有一个很好的理由来添加像 Table.ReplaceTypeFromFirstRow(table as table) as table 这样的东西，因为用 M 构造类型非常困惑。