// 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
背景
每当我有数据表格的布局不同,我就需要重新编写查询。在一个幻想世界中,您可以使用特定的键扩展所有具有记录的列。 理想情况下,您将拥有以下库函数:
// 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
问题
- 您能否获取具有特定类型的列列表,该类型未在表中指定,也就是推断出它?
- 您能使记录扩展通用吗?