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

问题描述

背景:

我发布了一个关于自定义函数的问题在我在 Chris Webb 我已经得到了答案.但是现在我有另一个与同一个自定义函数相关的问题.

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.

该自定义函数中令人惊奇的步骤之一是名为OutputTable"的递归步骤，它使用 if 语句调用自身，基本上使其成为循环.下面是步骤:

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)

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

问题:

在这一步之后我想做的是能够在 OutputTable 上添加更多转换.

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

例如，我想在所有行中添加一个只有A"的列.这样做的语法是 AddNewColumn = Table.AddColumn(OutputTable, "Test", each "A").但是当我这样做时，这给了我一个错误，说测试"列已经存在.但我确信没有其他名为Test"的列.即使我尝试将列的名称更改为其他名称，我也会收到相同的错误.

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.

注意:虽然我要添加的实际步骤不是 AddColumn，但如果我得到解决方案，我想我可以弄清楚那部分.

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.

源代码:

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.

假设我们有一个表，其中有两列 Col1 和 Col2 需要扩展.如果您在 OutputTable 步骤之后添加新列，您将获得:

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:

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

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.在尝试添加之前检查该列是否存在.

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

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