# Power Query Field Access Error



## cr731 (Apr 26, 2017)

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,


Dept CategoryDepartment Range StartDepartment Range End03300399044004990550059906600699

<tbody>

</tbody>
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 (Apr 26, 2017)

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 (Apr 26, 2017)

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 (Apr 26, 2017)

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 (Apr 26, 2017)

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


----------

