Power Query Field Access Error

[cr731]

I'm trying to use Power Query to fill in a list of department numbers and am getting an unfamiliar error.

I have a table with a listing of numbers like this,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Dept Category[/TD]
[TD]Department Range Start[/TD]
[TD]Department Range End[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD]300[/TD]
[TD]399[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD]400[/TD]
[TD]499[/TD]
[/TR]
[TR]
[TD]05[/TD]
[TD]500[/TD]
[TD]599[/TD]
[/TR]
[TR]
[TD]06[/TD]
[TD]600[/TD]
[TD]699[/TD]
[/TR]
</tbody>[/TABLE]

And the ultimate goal is to create a list of values with everything between Start Number and End Number, so for the above, a list of values 300-699.

I'm trying to insert the following calculated column

= Table.AddColumn(#"Changed Type1", "Department Range", each List.Generate( () => [Department Range Start], each _ <= [Department Range End], each _ +1))

I get the following error on the row with 300-399 (same error for the others but the Value changes)

Expression.Error: We cannot apply field access to the type Number.
Details:
Value=300
Key=Department Range End

What am I missing here... or is there a better way?

 

[cr731]

Well I got it working with

={[Department Range Start]..[Department Range End]}

(thanks to this https://blog.crossjoin.co.uk/2016/0...-and-characters-in-power-bipower-query-lists/)

Though I'm still curious what the field access error was about...

 

[MarcelBeug]

The cause of the error is that field [Department Range End] is not known in the context of List.Generate, as it is not included in the first argument.
Using List.Generate here is like "shooting a mosquito with a canon".

A better solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Dept Category", Int64.Type}, {"Department Range Start", Int64.Type}, {"Department Range End ", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Department Range", each {[Department Range Start]..[#"Department Range End "]})
in
    #"Added Custom"

 

[MarcelBeug]

Just to illustrate, this code is a working version of using List.Generate in this case.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Dept Category", Int64.Type}, {"Department Range Start", Int64.Type}, {"Department Range End", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", 
                                      "Department Range", 
                                      each List.Generate( () => [Counter = _[Department Range Start], End = _[Department Range End]], 
                                                         each [Counter] <= [End], 
                                                         each [Counter = [Counter] + 1, End = [End]], 
                                                         each [Counter]))
in
    #"Added Custom"

 

[MarcelBeug]

And another List.Generate solution, closer to the original code (I'm still discovering the secrets of List.Generate....)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Dept Category", Int64.Type}, {"Department Range Start", Int64.Type}, {"Department Range End", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Department Range", (CR) => List.Generate( () => CR[Department Range Start], each _ <= CR[Department Range End], each _ +1))
in
    #"Added Custom"