Best Practice: List.Generate in M

[Kealkil4]

Hi,

I am using List.Generate to create a new table based on the contents of 2 independent tables.

The number of rows in each is a variable and I may be either Merging or Inserting columns from the Input tables in the generated table.   The process throws an error when I reference a null value in Either of the input tables on reaching end of input.

What is best practice:

a) to get around the error by checking that the the Row Number is less than the Max number anywhere reference is made to a column from one of the input tables

or

b) should I let the "try ... otherwise" clause do the work.

I could be repeating this up to 10 times within the List.Generate statement.

Logic suggests that I ill I get a performance improvement by using the  try ... otherwise structure as it will only kick in at the end of file scenario.

Would appreciate any comments.

[hohlick · Accepted solution]

Hi @Kealkil4

It is hard to give you an answer that 100% fits your needs, and it is better if you can provide a little bit more detailed description of your algorythm - because best practice is depends on what you really want to do with your tables.

In the most cases the main pitfall of List.Generate is the correct use of second parameter, which is used to check the condition to stop/continue list generation. The pitfall (not obvious) is that this parameter executes BEFORE the third step is actually performing.

You can consider the next steps of evaluation:

1. first argument evaluated
2. second parameter checks the evaluation result of the previuos step.
3. If the result of step 2 is true, then record value (evaluated step 1) is used as output result, or it could be passed to the 4th parameter which generates the resulting list element. If the result of step 2 is false, then function stops.
4. then (at last) the third argument evaluated
5. GOTO step 2

You can see that List.Generate uses the next algorythm:

A = Evaluate arg1
Do While <arg2 condition>
    list element = A or arg4
    A = evaluate arg3
Loop

You can check example here:

let
    sTable= #table({"1"},{{"a"},{"b"},{"c"}}),
    Source = List.Generate(()=> [ x = 0, y=Table.RowCount(sTable),z=sTable{x}[1]] , each [x] < [y] , each [x = [x]+1, y=[y], z=sTable{x}[1]]),
    Custom1 = Table.FromRecords(Source)
in
    Custom1

where I generate a simple table with one column, then it generate a list of records, each record has a row index (base 0), number of rows in the source table, and values from this table.

Actualy you can chose, either to use row number to check or use any other check as the 2nd argument, but to avoid errors you should consider List.Generate evaluation algorythm

[Kealkil4]

Hi @hohlick,

Thank you for feedback.

The LIst.Generate clause is complex to say the least ... and too long to post here.

Because I am taking data from 2 input tables, the Exit checking condition has to be an AND combination of the Row Counts of the Input tables.   And either table could trigger the exit.

The number of records in the generated list will be < sum of the Row Counts in the input table.

I was either getting a record with errors in every field ... or the process was not writing out the last record.

Originally I had a single 'try ... otherwise" but was still getting errors as many of these were needed.

So to get to fully understand the problem, I put in checks to ensure that, when using an index to load the data from either of the Input Tables, the index was <= to the Row Count of that table.

So now that I have Query running without any errors ... and 'kind of' understand the pitfall you refer to ... I was wondering if I should leave the Row Count checking as is ... or relpace all of the checks with a 'try ... otherwise' combination.

Based on the feedback from yourself and @MarcelBeug  I am coming down in favour of the try ... otherwise.   The error only kicks in at end of file(s) and it will avoid rather having to test the Row Count on every iteration.

I am also conscious of the performance implications the @MarcelBeug refers to ... but I don't think I have any alternative other than to use List.Generate!!

Besides the number of records in both of the Input Tables will be <= 20.

Thanks to you both.

[MarcelBeug]

In my view "try ... otherwise" (and even "List.Generate") should be avoided whenever possible.

But without detailed information it is impossible to provide a specifc answer.