强大的查询递归功能：在递归步骤之后添加更多步骤 [英] Power query recursive function: add more steps after the recursive step

问题描述

背景：

我已经发布了 / 2014/05/21 / expanding-all-columns-in-a-table-in-power-query / rel = nofollow noreferrer>克里斯·韦伯，我已经得到了答案。但是现在我还有一个问题与同一个自定义函数有关。

该自定义函数中令人惊讶的步骤之一是最后一个名为 OutputTable ，它使用if语句进行自我调用，基本上是使其成为循环。下面是步骤：

OutputTable =如果NextColumnNumber>（Table.ColumnCount（ExpandedTable）-1）然后ExpandedTable否则 ExpandAll （ExpandedTable，NextColumnNumber）

问题：

现在我想在此步骤之后要做的就是能够在OutputTable上添加更多的转换。

例如，我想添加一个在所有行中仅包含 A的列。这样做的语法是 AddNewColumn = Table.AddColumn（OutputTable， Test，每个 A）。但是，当我这样做时，这会给我一个错误，表明测试列已存在。但是，我确定没有其他列名为测试的列。即使我尝试将列名更改为其他名称，也会遇到相同的错误。

注意：尽管我要添加的实际步骤不是AddColumn，但是认为我可以解决这一问题。

源代码：

  let 
 Source =（TableToExpand作为表，可选的ColumnNumber作为数字）=> 
 let 
 ActualColumnNumber = if（ColumnNumber = null）然后为0 else ColumnNumber，
 ColumnName = Table.ColumnNames（TableToExpand）{ActualColumnNumber}，
 ColumnContents = Table.Column（TableToExpand， ColumnName），
 ColumnsToExpand = List.Select（List.Distinct（List.Combine（List.Transform（ColumnContents，如果_是表，则每个Table.ColumnNames（_）else {}）））），每个（_ =  view或_ = viewfolder或_ = Attribute：name）），
 NewColumnNames = List.Transform（ColumnsToExpand，每个ColumnName&。& _），
 CanExpandCurrentColumn = List.Count（ColumnsToExpand）> 0，
 ExpandedTable =如果CanExpandCurrentColumn则Table.ExpandTableColumn（TableToExpand，ColumnName，ColumnsToExpand，NewColumnNames）否则TableToExpand 
 NextColumnNumber =如果CanExpandCurrentColumn则ActualColumnNumber否则ActualColumnNumber + 1，
 OutputTable =如果NextColumnNumber>（Table.ColumnCount（ExpandedTable）-1）然后ExpandedTable否则Expa ndAll（ExpandedTable，NextColumnNumber）
 in 
 OutputTable 
 in 
来源
  

假设我们有一个包含两列 Col1 和 Col2的表需要扩展。如果在 OutputTable 步骤之后添加新列，则会得到：

 开始：Col0，Col1，Col2 
 OutputTable（1）：Col0，Col1.a，Col1.b，Col2 
 OutputTable（2）：Col0，Col1.a，Col1.b，Col2 .x，Col2.y，Col2.z，测试
 AddNewColumn：Col0，Col1.a，Col1.b，Col2.x，Col2.y，Col2.z，测试，测试
  

以下是几种尝试方法：

1。

我认为您可以通过更改 OutputTable 行来做到这一点。如下所示：

  OutputTable =如果NextColumnNumber>（Table.ColumnCount（ExpandedTable）-1）
然后是Table.AddColumn （ExpandedTable，测试，每个 A）
 else ExpandAll（ExpandedTable，NextColumnNumber）
  

< h3> 2。

  AddNewColumn = if Table.HasColumns（OutputTable， Test）
然后OutputTable 
 else Table.AddColumn（OutputTable， Test，每个 A）
  

Background:

I have posted a question regarding a custom function in Power Query that I found in a blog by Chris Webb which I have already got an answer to.But now I have another question related to the same custom function.

One of the amazing steps in that custom function is the recursive step at the end named "OutputTable" which calls itself using a if statement, basically making it a loop. Below is the step:

OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else ExpandAll(ExpandedTable, NextColumnNumber)

Question:

Now what I would like to do after this step is to be able to add more transformation on the OutputTable.

For Example, I would like to add a column with just "A" in all the rows. The syntax to do that would be AddNewColumn = Table.AddColumn(OutputTable, "Test", each "A"). But when I do this this gives me an error saying that the column "Test" already exists. But i'm sure that there is no other column with name "Test". Even if I try changing the name of the column to anything else, I get the same error.

Note: Although the actual step I want to add is not AddColumn, I think I can figure out that part If I get a solution for this.

SourceCode:

let
    Source = (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.Select(List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))), each (_ = "view" or _ = "viewfolder" or _ = "Attribute:name")),
     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
in
    Source

解决方案

I'm guessing it's throwing the error due to the recursive nature of the function calling itself and trying to apply the new column twice, once in the innermost loop and once in the outermost loop.

Let's say we have a table with two columns Col1 and Col2 that need to be expanded. If you add the new column after the OutputTable step, you'll get:

Start:          Col0, Col1, Col2
OutputTable(1): Col0, Col1.a, Col1.b, Col2
OutputTable(2): Col0, Col1.a, Col1.b, Col2.x, Col2.y, Col2.z, Test
AddNewColumn:   Col0, Col1.a, Col1.b, Col2.x, Col2.y, Col2.z, Test, Test

Here are a couple of approaches to try:

1. Only try to add the column when recursion is finished.

I think you can do this by changing your OutputTable line as follows:

OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
              then Table.AddColumn(ExpandedTable, "Test", each "A")
              else ExpandAll(ExpandedTable, NextColumnNumber)

2. Check if the column exists before trying to add it.

AddNewColumn = if Table.HasColumns(OutputTable, "Test")
               then OutputTable
               else Table.AddColumn(OutputTable, "Test", each "A")

这篇关于强大的查询递归功能：在递归步骤之后添加更多步骤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！