Powerquery:展开其中有记录的所有列 [英] Powerquery: Expand all columns of that have records in them

问题描述

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

Using Power Query in Microsoft Excel 2013, I created a table that looks like this:

// 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 列在这两个记录上.手动，我会这样做:

Now, I want to expand the columns Address and Wife by using the name attribute on both records. Manually, I would do it like this:

// 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 的列他们使用特定的键.理想情况下，您将拥有以下库功能:

// 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 行.

推荐答案

(首先，感谢清晰的解释和示例数据和建议！)

(First off, thanks for the clear explanation and sample data and suggestions!)

1) M 代码中无法进行类型推断.这个限制几乎可以被认为是一个特性"，因为如果源数据的变化导致推断类型不同，它几乎肯定会破坏您的查询.

1) There's no way in M code to do type inference. This limitation might almost be considered a "feature", because if the source data changes in a way that causes the inferred type to be different, it will almost certainly break your query.

一旦你加载了你的非类型化数据，使用检测数据类型 按钮为此生成 M.或者，如果您正在从 JSON 读取数据，那么它的类型应该已经足够了.

Once you load your untyped data, it should be quick to use the Detect Data Type button to generate the M for this. Or if you are reading data from JSON it should be mostly typed enough already.

如果您有一个不起作用的特定场景，想要更新您的问题吗?:)

If you have a specific scenario where this doesn't work want to update your question? :)

2) 只要​​表格的单元格值是记录，就很有可能使记录扩展变得通用，而且只是有点复杂.这将查找所有行都是 null 或记录的列，并展开 name 列.

2) It's very possible and only a little convoluted to make the record expansion generic, as long as the cell values of the table are records. This finds columns where all rows are either null or a record and expands the name column.

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

Here's some simple implementations you can add to your library:

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 构造类型非常混乱.

You might have a good argument for adding something like Table.ReplaceTypeFromFirstRow(table as table) as table, because constructing the type with M is very messy.

这篇关于Powerquery:展开其中有记录的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！