Inserting rows recursively does not yield correct results

[Anonymous]

I have a function that adds multiple rows recursively to a table. But this function only appends a single row correctly. Recursively calling it does not work.

The function:

(NDaysToExtend as number, Offset as number, CurrentLoop as number, LastBurndownDate as date, LastIndex as number, TestlineDate as date) =>

let
nLoop = CurrentLoop + 1,
nDate = Date.AddDays(LastBurndownDate, nLoop),
nIndex = LastIndex + nLoop,
nOffset = Offset + 1,

NewRow =
if (nLoop - 1) < NDaysToExtend
then
{
Table.InsertRows(#"Burndown Query (2)", nOffset, {[DateValue=nDate, Unique Dates=nDate, New.1=0, Active.1=0, Closed.1=0, Resolved.1=0, Accepted.1=0, Next ER.1=0, In Development.1=0, In Test.1=0, Pending Defects.1=0, Awaiting Acceptance Criteria.1=0, Ready for Development.1=0, Ready for Test.1=0, Proposed.1=0, Index=nIndex, Testline Dates=TestlineDate]}),

@InsertTrendRows(NDaysToExtend, nOffset, nLoop, nDate, nIndex, TestlineDate)
}
else
null
in
NewRow

The inputs:

CurrentLoop = 0, LastIndex= 41, offset = 41, nDaysToExtend = 3, LastBurndownDate= 6/2/2021, Testline Date = 6/5/2021

The output:

The expectation is: Add 3 rows after row 42 with dates incremented and all values to be zero. 

But only one row is inserted correctly. Calling the same function recursively does not add other 2 rows. Is there something I am doing incorrectly?

Please help.

Thanks

[Anonymous]

I would be nicer if any working function as an example is presented here so that I can check it at my end. Table columns and data is not important, it's the logic to insert the rows recursively that matters most. I will then invoke this function as an action step for my existing table.

Or may be I am wrong to dynamically insert rows in an existing table alltogether. Any nicer way to deal with this situation would be highly appreciated.

Thanks.

[d_gosbell]

The main issue here is that Table.InsertRows does not alter the table you pass in, it actually returns a new table object with the additional rows added to that new object. Once it is declared rhe #"Burndown Query (2)"table cannot be altered.

So you would need to alter your function to accept a table as a parameter and then return this new table object.

Then you could write a query like the following in invoke your function.

let

   Source = #"Burndown Query (2)",
   #"Added Rows" = myFunction( Source, 0,  41,  41,  3,  6/2/2021,  6/5/2021)
in
  #"Added Rows"

[Anonymous]

I tried altering my function to accept modified table as on object like this:

(NTable as table, NDaysToExtend as number, Offset as number, CurrentLoop as number, LastBurndownDate as date, LastIndex as number, TestlineDate as date) =>

let
nLoop = CurrentLoop + 1,
nDate = Date.AddDays(LastBurndownDate, nLoop),
nIndex = LastIndex + nLoop,
nOffset = Offset + 1,
nTable = #"Burndown Query (2)",
ModTable = #"Burndown Query (2)",

NewTable =
if CurrentLoop = 0
then nTable = #"Burndown Query (2)"
else
nTable = NTable,

NewRow =

if (nLoop - 1) < NDaysToExtend
then
{
ModTable = Table.InsertRows(nTable, nOffset, {[DateValue=nDate, Unique Dates=nDate, New.1=0, Active.1=0, Closed.1=0, Resolved.1=0, Accepted.1=0, Next ER.1=0, In Development.1=0, In Test.1=0, Pending Defects.1=0, Awaiting Acceptance Criteria.1=0, Ready for Development.1=0, Ready for Test.1=0, Proposed.1=0, Index=nIndex, Testline Dates=TestlineDate]}),

@#"InsertTrendRows"(ModTable, NDaysToExtend, nOffset, nLoop, nDate, nIndex, TestlineDate)
}
else
ModTable = nTable
in
NewRow

But this resulted in error and no single row was inserted. Is there anything else missing in here?

[Anonymous]

I get this output:

while I expected a new table with inserted rows.

[Anonymous]

Hi @Anonymous,

It seems like you invoke another query table in your custom function so that we can't test with the sample codes. Can you please share the table schema that referenced in your function?

How to Get Your Question Answered Quickly 

BTW, I think this scenario should more relate to your conditions. The function used the 'n Loop' variable to handle recursive calculations. When it processes at the last row, its recursive has been exited after the template row generated without any loopings. (when 'nloop' to 43, the 'InsertTrendRows' part will be canceled due to the new loop not suitable for if statements and it will return null)

In my opinion, I'd like to suggest you add a variable to check and pass the generated table rows and use it as exit conditions.
Regards,

Xiaoxin Sheng

[Anonymous]

I tried altering my function to accept modified table as on object like this:

(NTable as table, NDaysToExtend as number, Offset as number, CurrentLoop as number, LastBurndownDate as date, LastIndex as number, TestlineDate as date) =>

let
nLoop = CurrentLoop + 1,
nDate = Date.AddDays(LastBurndownDate, nLoop),
nIndex = LastIndex + nLoop,
nOffset = Offset + 1,
nTable = #"Burndown Query (2)",
ModTable = #"Burndown Query (2)",

NewTable =
if CurrentLoop = 0
then nTable = #"Burndown Query (2)"
else
nTable = NTable,

NewRow =

if (nLoop - 1) < NDaysToExtend
then
{
ModTable = Table.InsertRows(nTable, nOffset, {[DateValue=nDate, Unique Dates=nDate, New.1=0, Active.1=0, Closed.1=0, Resolved.1=0, Accepted.1=0, Next ER.1=0, In Development.1=0, In Test.1=0, Pending Defects.1=0, Awaiting Acceptance Criteria.1=0, Ready for Development.1=0, Ready for Test.1=0, Proposed.1=0, Index=nIndex, Testline Dates=TestlineDate]}),

@#"InsertTrendRows"(ModTable, NDaysToExtend, nOffset, nLoop, nDate, nIndex, TestlineDate)
}
else
ModTable = nTable
in
NewRow

But this resulted in error and not even a single row was inserted. Is there anything else missing in here?

[d_gosbell · Accepted solution]

There are a number of other issues in that code still. M is a functional language, and in those variables are typically immutable and you are trying to re-assign values to an existing variable which will not work. I'm not entirely clear on what it is you are trying to achieve. But to add a list of rows to a table you could use a pattern combining Table.Combine and List.Generate

The following example starts off with a single row and generates 3 new rows with incrementing dates.

eg

let
    nDate = #date(2021, 5, 19),
    nLoop = 3,
    nMaxIndex = 10,

    // Create inital row
    Initial ={[DateValue=nDate, Unique Dates=nDate, New.1=0, Active.1=0, Closed.1=0, Resolved.1=0, Accepted.1=0, Next ER.1=0, In Development.1=0, In Test.1=0, Pending Defects.1=0, Awaiting Acceptance Criteria.1=0, Ready for Development.1=0, Ready for Test.1=0, Proposed.1=0, Index=nMaxIndex]},
    tbl = Table.FromRecords(Initial),
    
    // Combine the initial data with the generated records
    Source = 
        Table.Combine({tbl, 
            Table.FromRecords( 
                // Generate a list of records
                List.Generate(
                    () => [DateValue=Date.AddDays(nDate,1), 
                        Unique Dates=Date.AddDays(nDate,1), 
                        New.1=0, 
                        Active.1=0, 
                        Closed.1=0, Resolved.1=0, 
                        Accepted.1=0, 
                        Next ER.1=0, 
                        In Development.1=0, 
                        In Test.1=0, 
                        Pending Defects.1=0, 
                        Awaiting Acceptance Criteria.1=0, 
                        Ready for Development.1=0, 
                        Ready for Test.1=0, 
                        Proposed.1=0, 
                        Index=nMaxIndex],
                    each [Index] < nMaxIndex + nLoop,
                    each [Index = [Index] + 1, 
                        DateValue= Date.AddDays([DateValue],1),
                        Unique Dates=Date.AddDays([DateValue],1), 
                        New.1=0, 
                        Active.1=0, 
                        Closed.1=0, 
                        Resolved.1=0, 
                        Accepted.1=0, 
                        Next ER.1=0, 
                        In Development.1=0, 
                        In Test.1=0, 
                        Pending Defects.1=0, 
                        Awaiting Acceptance Criteria.1=0, 
                        Ready for Development.1=0, 
                        Ready for Test.1=0, 
                        Proposed.1=0]
                )
            )}
        )
in
    Source

[Anonymous]

Thanks @d_gosbell 

This worked like a charm. Infact I was also trying to use List.Generate function but since I am new to M language, I hardly get it working. Actually I am moving from C# to M language and hence, constructs are very different here.

Thanks again,

Best Regards,

Nitin

[Anonymous]

I get this output:

while I expected a new table with inserted rows.