Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
Index=-1
It is a long time since I have made it, but I believe the relevant step was trying to change a column name if that column name existed in the dataset:
= Table.RenameColumns(Custom1,{{Table.ColumnNames( #"Promoted Headers"){List.PositionOf(List.Transform(Table.ColumnNames( #"Promoted Headers"),each Text.Contains(_,"Record ID")),true)}, "Main Record ID"},{Table.ColumnNames( #"Promoted Headers"){List.PositionOf(List.Transform(Table.ColumnNames( #"Promoted Headers"),each Text.Contains(_,"Associated Contact")),true)}, "New Associated Contact"},{Table.ColumnNames( #"Promoted Headers"){List.PositionOf(List.Transform(Table.ColumnNames( #"Promoted Headers"),each Text.Contains(_,"Associated Company ID")),true)}, "New Associated Company ID"} })
Any help would be appreciated. I have tried fixing it myself, but have limited PQ knowledge.
Thanks!
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI use try catch/otherwise to catch error from List.PositionOf()
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting