Power query recursive function: add more steps after the recursive step

1
votes

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
1
excelpowerbipowerquerym

1 Answers

1
votes

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