Cannot pass column values to List.Generate()

[Ron Coderre]

Here's my issue....(File attached)

I want to use this table to set the Start, End, and Step values when creating a list for each row.

Name    Start1    End1    Step1
Alpha        1      10        1
Bravo        4       8        2

My first successful attempt, which adds a column of Lists, uses hard-coded values:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        List.Generate(()=>1,
            each _ <= 10,
            each _ + 2))
in
    #"Added Custom"

My second UN-successful attempt, uses column values in the List.Generate:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
        List.Generate(
            ()=> _[Start1],
            each _ <= [End1],
            each _ + [Step1]))
in
    #"Added Custom"

It seems to work...but each List in the added column resolves to this error:
Expression.Error: We cannot apply field access to the type Number.

However, if I create this function to create a list:

(Start as number, End as number, Step as number) =>
let
    MyList =
        List.Generate(()=>Start,
            each _ <= End,
            each _ + Step)
in
    MyList

then this works:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each fnCreateList([Start1],[End1],[Step1]))
in
    #"Added Custom"

I don't understand why I can pass field values to the function, but cannot use them directly.

 

[miguel]

Hey Ron,
This one would also work:

= Table.AddColumn(Source, "Custom", each let 
Start =  [Start1],
End = [End1],
Step = [Step1],
       MyList= List.Generate(
            ()=> Start,
            each _ <= End,
            each _ + Step)
        in
        MyList)

The situation that you're facing is basically a mix of inner and outer row context (because of the each and underscore which don't know if you're referring to the current list value or the current row of the table). This is one of the most complicated topics of the M language and you could go down the rabbit hole to figure things out, but ultimately the best approach would be to create a custom function or declare the variables as shown in the example above.

 

[Ron Coderre]

Thanks for taking the time to work on my issue, Miguel.
I was hoping to find an elegant approach. I'd tried all kinds of combinations of "each" and "this"... to no avail.
It seems that there should be a way to bridge the gap between inner and outer references. I'll probably keep poking around to see if I can find a way to do that.

Again, thank you.

 

[miguel]

there is a way. It's just not elegant and it's pretty time consuming.
Here's a video that tries to explain the inner vs outer row context in Power Query.

 

[Ron Coderre]

Thanks, again, Miguel.
I watched the video several times, but couldn't get the principle to apply to my situation. I'll keep experimenting.

 

[miguel]

Ron,

I see now that you're after more of a "why this is happening" rather than "how can I get this results". Usually the people that post here are only after the result, so this is kinda new to me.
What you're after is this:

= Table.AddColumn(Source, "Custom", each        List.Generate(
            ()=> _[Start1],
            (x) => x <= _[End1],
            (x) => x + _[Step1]))

The reason behind this is that you can't use the underscore (_) to call an inner and an outer row context at the same time, so you need to define the inner row context (the row context within the list.generate function) as an alternate to the underscore. In my case I'm using an 'x', but you really can use any other letter or phrase that you want. That's the main concept behind the video that I provided. Again, it's one of those really complicated topics because it deserves some sort of diagram to explain how the row context works in PQ, but is not something that most people are after. To be honest, even pros at PQ don't usually take a look at List.Generate or Row Context - they just build the function like you did and that's it.

I'm hoping that this gives you the information that you were looking for.

Best!

 

[Ron Coderre]

That did it, Miguel! Between your explanation and example, I understand what was happening. I thought the outer context wasn't percolating down to the List.Generate, but I was wrong. I needed to explicitly indicate the inner context to the function.
Here's what I used (and what I put in my database of Excel/Programming/SQL instructions):

    Added_Custom = Table.AddColumn(Source, "Custom", each
        List.Generate(
            ()=> [Start1],
            (listval) => listval <= [End1],
            (listval) => listval + [Step1])),

I really appreciate your patience in assisting me. That technique will help me streamline a PQ process that forecasts the future impacts of tiered cashflows totalling hundreds of millions of dollars....and...It's elegant! As an Excel Pro, it's important to me that my work exhibits a bit of virtuosity.

 

[harlanwf]

Context Error Continues After Assigning Functions

Ron,
I applied your approach described here and in your blog post (https://www.excelguru.ca/blog/2019/06/05/create-two-tiered-multi-frequency-period-cycles/), and List.Generate throws the similar context error with (indexer). What am I missing?

View attachment list.generate tester2.xlsx

Add_List_Generate_result_column = Table.AddColumn(count_as_number, "list_generate_tester", (each
List.Generate(()=>
Text.Combine({[First Letters ]{indexer},":",[sentences]{indexer},";",[reasons]{indexer}}),
(indexer)=> indexer<=([count of First Letters] -1),
(indexer)=> indexer+1)))
Harlan F.