Forum Discussion
Solved
Expression.Error: The index cannot be negative.
I have been using this query for some time now, but suddenly it throws up the following error when I am trying to run it: Expression.Error: The index cannot be negative. Details: Value=[List] ...
List.PositionOf() returns -1 if the thing you are looking for isn't found. So either Record ID, Associated Contact or Associated Company ID isn't being found.
Note that Power Query is case sensitive, so you might have "RECORD ID" or "record ID" or "Record Id" and that doesn't count if you are searching for "Record ID"
If case sensitivity is the issue, then change your Text.Contains() functions to:Text.Contains(Text.Lower(_), "record id")That will convert whatever is coming in to lower case and you compare against that.
I was trying to lookup the Holiday name - IF it exists -- so, I fully expect it to not find anything most of the time. With Arielcchy's advise, I came up with this, which seems to be working:
#"Added IsHoliday" = Table.AddColumn(#"Added Is6weeksPlusMinus", "IsHoliday", each if (try DimHolidays[Holiday]{List.PositionOf(DimHolidays[Date], [Date])} otherwise "None") = "None" then "No" else "Yes", type text )