Expression.Error: We cannot apply field access to the type Null.

[mike_asplin]

I have a table with 40 columns that contain either null or Y for each row. I'd like to count how many "Y" there are. I found some help from other posts, but doesnt seem to work.

My calcuated column is with a few of the columns

= Table.AddColumn(#"Replaced Value", "Custom", each List.Count(List.Select({[#"Service type - Acute services with overnight beds"],[#"Service type - Acute services without overnight beds / listed acute services with or without overnight beds"],[#"Service type - Ambulance service"],[#"Service type - Blood and Transplant service"],[#"Service type - Care home service with nursing"],[#"Service type - Care home service without nursing"],[#"Service type - Community based services for people who misuse substances"][#"Service type - Community based services for people with a learning disability"]}, each _ ="Y")))

but this throws the error

Expression.Error: We cannot apply field access to the type Null.
Details:
Value=
Key=Service type - Community based services for people with a learning disability

I tried replacing the null with N in this specific column but same error

Any help appreciated as sure its a small thing

Thanks

[mike_asplin]

what a donkey. Thanks so much

[Shahid12523 · Accepted solution]

Expression.Error: We cannot apply field access to the type Null

is caused by a missing comma between two field references inside your list. Specifically, this part

[#"Service type - Community based services for people who misuse substances"][#"Service type - Community based services for people with a learning disability"]

= Table.AddColumn(#"Replaced Value", "Custom", each
List.Count(
List.Select({
[#"Service type - Acute services with overnight beds"],
[#"Service type - Acute services without overnight beds / listed acute services with or without overnight beds"],
[#"Service type - Ambulance service"],
[#"Service type - Blood and Transplant service"],
[#"Service type - Care home service with nursing"],
[#"Service type - Care home service without nursing"],
[#"Service type - Community based services for people who misuse substances"],
[#"Service type - Community based services for people with a learning disability"]
}, each _ = "Y")
)
)