Forum Discussion
List.Transform Error when using List.Contains or List.ContainsAny but not when using OR comparison.
Hello,
I am working on some transformation, and I have found out behavior of PQ that I dont understand.
When I am using the code:
= List.Transform( Custom3{0}[Record], each if _ = "2024" or _ = "2023" or _ = "2022" or _ = "2021"or _ = "2020" then null else _ )
all is working as I want.
Now I wished to clean the code a bit so instead of listing the years 1 by 1 I decided to use List.Contains and List.Contains.Any
In it I decided to use numerical text {"2020".."2030"} and this gave me the error
the issue / question is how do I generate a range from 2020 to 2030 as text values.
also why cant I genare sequence of "2020" to "2030" ?
3 Replies
you can use this formula instead
=each List.Contains({"2022","2023","2024","2025","2026"}, _ )The .. notation only works with integers or with single characters. For example {"A".."Z"} works but {"AA".."ZZ"} doesn't.
Create a numeric list {2020..2030} and then use Text.From(_)
= List.Generate(()=> 2020, each _ < 2031 , each _ +1, each Text.From(_))
or
= List.Transform({2020..2030},each Text.From(_))
