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

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