从json动态扩展所有列表和记录 [英] Dynamically expand ALL lists and records from json

问题描述

我想在 json 响应中展开所有列表和记录.列就像例如(这是动态的，也可以是10条记录和5条列表):

I want to expand all lists and records in a json response. Columns are like e.g. (this is dynamically, it also can be 10 records and 5 lists):

文本、文本、[列表]、[列表]、文本、[记录]、[记录]、字符串、[记录]

Text, Text, [List], [List], Text, [Record], [Record], String, [Record]

我编写了一个函数来获取具有特定类型的所有列

I wrote a function for getting all columns with the specific type

Cn.GetAllColumnsWithType = (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;

和一个从表中展开所有列表的函数

and a function to expand all lists from a table

Cn.ExpandAllListsFromTable = (table as table, columns as list) =>
let
    expandedListTable = List.Accumulate(columns, table, (state, columnToExpand) =>
        Table.ExpandListColumn(state, columnToExpand))
in
    expandedListTable;

所有列表现在都是记录，我想动态扩展所有这些记录.

all lists are now records and i want to dynamically expand all these records.

我想我需要一个 foreach 来遍历列表(这只是 Cn.GetAllColumnsWithType 的记录原因)，Table.ExpandRecordColumn 每个元素都带有 Table.ColumnNames 并将其添加到表中，但我不知道该怎么做.也许你可以帮助我，因为它让我发疯.干杯

I think i need a foreach to iterate through the list (which are only records cause of Cn.GetAllColumnsWithType), Table.ExpandRecordColumn each element with it's Table.ColumnNames and add it to the table but i don't know how to do it. Maybe you can help me out cause it's driving me crazy. Cheers

我最近开了一个帖子，但我想扩展一个特定的帖子，比如

I recently opened a thread but there i wanted to expand a specific one like

#"SelectItems" = Table.SelectColumns(records,{"$items"}),
#"$items1" = #"SelectItems"{0}[#"$items"],

但现在我想动态地做这一切.

but now i want to do it all dynamically.

推荐答案

Chris Webb 编写了一个函数来为表类型的列执行此操作:

Chris Webb wrote a function to do this for Table-type columns:

http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/

我分享了我为记录类型列制作的调整版本:

I've shared a tweaked version of that that I made for Record-type columns:

https://gist.github.com/Mike-Honey/0a252edf66c3c486b69b

这篇关于从json动态扩展所有列表和记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！