代码之家 › 专栏 › 技术社区 › Gangula

自动展开具有特定名称的所有“表列”

m powerquery powerbi excel

2

Gangula · 技术社区 · 8 年前

我是一个编程初学者,不熟悉“m”建模语言。我有一个XML文件,想用来在查询编辑器中加载数据。在查询中,我只需要展开具有以下特定名称的表列:

看法
视图文件夹
属性:名称

我发现了 post 他们给一个 function by Chris Webb 用于展开所有列表(代码下面)。

= (TableToExpand as table, optional ColumnNumber as number) =>
    let
     ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
     ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
     ColumnContents = Table.Column(TableToExpand, ColumnName),
     ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
     NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
     CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
     ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
     NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
     OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else ExpandAll(ExpandedTable, NextColumnNumber)
    in
     OutputTable

但是如何只扩展所需的列表/表呢?

1 回复 | 直到 7 年前

1

Alexis Olson 8 年前

你应该可以简单地在旧的 ColumnsToExpand 线。

也就是说,

 TableColumns = //This is the old ColumnsToExpand definition renamed.
     List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
 ColumnsToExpand =
     List.Select(TableColumns, each (_ = "view" or _ = "viewfolder" or _ = "Attribute:name")),

或者像这样排成一行,

 ColumsToExpand = List.Select(List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))), each (_ = "view" or _ = "viewfolder" or _ = "Attribute:name")),