Forum Discussion
Excel Power Query value with leading space cannot covert
Below is my code in Power Query within Excel to compare the next value to the current value.
= Table.AddColumn(#"Added Index", "Original Contract2", each if [Order Group Primary Number] {[Index] + 1} = [Order Group Primary Number] {[Index]} then null else [Original Contract])
I am getting the error below. I had changed the type of the [Order Group Primary Number] to text in a step above. Any assistance will be greatly appreciated with this leading space.
Expression.Error: We cannot convert the value " 134955" to type List.
10 Replies
You need to reference entire column with numbers, not each element in it. That could be as
#"Added Custom" = Table.AddColumn( #"Added Index", "Custom", each if #"Added Index"[Order Group Primary Number]{[Index] + 1} = #"Added Index"[Order Group Primary Number]{[Index]} then null else [Original Contract] )or
#"Added Custom" = Table.AddColumn( #"Added Index", "Custom", each [numbers= #"Added Index"[Order Group Primary Number], a = if #"Added Index"[Order Group Primary Number]{[Index] + 1} = #"Added Index"[Order Group Primary Number]{[Index]} then null else [Original Contract] ][a] )or like.
Alternatively you may add another Index (first starts from zero, another from 1), merge table with itself on these indexes, extract field with primary number and add conditional column.
Sorry, second variant is
#"Added Custom" = Table.AddColumn( #"Added Index", "Custom", each [numbers= #"Added Index"[Order Group Primary Number], a = if numbers{[Index] + 1} = numbers{[Index]} then null else [Original Contract] ][a] )- SergeiBaklan I am using the second variant of the code. I am now getting an "Index was outside the bounds of the array." Is there a way to validate that the index list has reached its max before adding "1"?
