自动展开所有“表格列”有特定名称 [英] Automatically Expand all "Table Columns" with specific name

问题描述

我是编码的初学者，不熟悉 M建模语言。我有一个XML文件，要用于在查询编辑器中加载数据。在查询中，我只需要展开以下具有特定名称的表列：

1. view


2. viewfolder


3. 属性：名称

我发现以下

解决方案

您应该能够简单地在旧过滤器上放置一个过滤器 ColumnsToExpand 行。

即，

  TableColumns = //这是重命名的旧ColumnsToExpand定义。 
 List.Distinct（List.Combine（List.Transform（ColumnContents，如果_是表，则每个Table.ColumnNames（_）else {}）））），
 ColumnsToExpand = 
 List.Select （TableColumns，每个（_ = view或_ = viewfolder或_ = Attribute：name）），
  

或者像这样一行，

  ColumsToExpand = List.Select（List.Distinct（List.Combine （List.Transform（ColumnContents，如果_是表，则每个，则Table.ColumnNames（_）else {}）））），每个（_ = view或_ = viewfolder或_ = Attribute：name））， 
  

I'm a beginner in coding and am not familiar with "M" modeling language. I have an XML file that I want to use to load the data in Query Editor. In the Query, I need to expand only the table columns with a specific name below:

1. view
2. viewfolder
3. Attribute:name

I have found the following post where they give a function by Chris Webb for expanding all the lists(below code).

= (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

But how to expand only desired lists/tables?

解决方案

You should be able to simply put a filter on the old ColumnsToExpand line.

That is,

 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")),

Or in one line like this,

 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")),

这篇关于自动展开所有“表格列”有特定名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！