Excel Power Query value with leading space cannot covert

Question

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.

sergeibaklan · Answer

Deirdre1720&nbsp;
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.

sergeibaklan · Answer

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

deirdre1720 · Answer

SergeiBaklan&nbsp; Thank you for both variants to resolve my issue.&nbsp; I tried both, but the cursor kept spinning.&nbsp; I may not have waited long enough for the results.&nbsp; So, I will try again.&nbsp; Thank you, again!

deirdre1720 · Answer

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

artup720 · Answer

Hi,Add statement try after each and otherwise null before last )Artur

Forum Discussion

Excel Power Query value with leading space cannot covert

10 Replies