Excel Power Query value with leading space cannot covert

%3CLINGO-SUB%20id%3D%22lingo-sub-2628542%22%20slang%3D%22en-US%22%3EExcel%20Power%20Query%20value%20with%20leading%20space%20cannot%20covert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2628542%22%20slang%3D%22en-US%22%3E%3CP%3EBelow%20is%20my%20code%20in%20Power%20Query%20within%20Excel%20to%20compare%20the%20next%20value%20to%20the%20current%20value.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20Table.AddColumn(%23%22Added%20Index%22%2C%20%22Original%20Contract2%22%2C%20each%20if%20%5BOrder%20Group%20Primary%20Number%5D%20%7B%5BIndex%5D%20%2B%201%7D%20%3D%20%5BOrder%20Group%20Primary%20Number%5D%20%7B%5BIndex%5D%7D%20then%20null%20else%20%5BOriginal%20Contract%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20getting%20the%20error%20below.%26nbsp%3B%20I%20had%20changed%20the%20type%20of%20the%20%5BOrder%20Group%20Primary%20Number%5D%20to%20text%20in%20a%20step%20above.%26nbsp%3B%20Any%20assistance%20will%20be%20greatly%20appreciated%20with%20this%20leading%20space.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpression.Error%3A%20We%20cannot%20convert%20the%20value%20%22%20134955%22%20to%20type%20List.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2628542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2629045%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20value%20with%20leading%20space%20cannot%20covert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F601028%22%20target%3D%22_blank%22%3E%40Deirdre1720%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20reference%20entire%20column%20with%20numbers%2C%20not%20each%20element%20in%20it.%20That%20could%20be%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Index%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20%20%23%22Added%20Index%22%5BOrder%20Group%20Primary%20Number%5D%7B%5BIndex%5D%20%2B%201%7D%20%3D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23%22Added%20Index%22%5BOrder%20Group%20Primary%20Number%5D%7B%5BIndex%5D%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20else%20%5BOriginal%20Contract%5D%0A%20%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Index%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20%5Bnumbers%3D%20%23%22Added%20Index%22%5BOrder%20Group%20Primary%20Number%5D%2C%0A%20%20%20%20%20%20%20%20a%20%3D%20if%20%20%23%22Added%20Index%22%5BOrder%20Group%20Primary%20Number%5D%7B%5BIndex%5D%20%2B%201%7D%20%3D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23%22Added%20Index%22%5BOrder%20Group%20Primary%20Number%5D%7B%5BIndex%5D%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20else%20%5BOriginal%20Contract%5D%0A%20%20%20%20%20%20%20%20%5D%5Ba%5D%0A%20%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20like.%3C%2FP%3E%0A%3CP%3EAlternatively%20you%20may%20add%20another%20Index%20(first%20starts%20from%20zero%2C%20another%20from%201)%2C%20merge%20table%20with%20itself%20on%20these%20indexes%2C%20extract%20field%20with%20primary%20number%20and%20add%20conditional%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2629081%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20value%20with%20leading%20space%20cannot%20covert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629081%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20second%20variant%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%20%20%20%20%23%22Added%20Custom%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Added%20Index%22%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20%5Bnumbers%3D%20%23%22Added%20Index%22%5BOrder%20Group%20Primary%20Number%5D%2C%0A%20%20%20%20%20%20%20%20a%20%3D%20if%20%20numbers%7B%5BIndex%5D%20%2B%201%7D%20%3D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20numbers%7B%5BIndex%5D%7D%0A%20%20%20%20%20%20%20%20%20%20%20%20then%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20else%20%5BOriginal%20Contract%5D%0A%20%20%20%20%20%20%20%20%5D%5Ba%5D%0A%20%20%20%20)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2629209%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20value%20with%20leading%20space%20cannot%20covert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2629209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20both%20variants%20to%20resolve%20my%20issue.%26nbsp%3B%20I%20tried%20both%2C%20but%20the%20cursor%20kept%20spinning.%26nbsp%3B%20I%20may%20not%20have%20waited%20long%20enough%20for%20the%20results.%26nbsp%3B%20So%2C%20I%20will%20try%20again.%26nbsp%3B%20Thank%20you%2C%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2670779%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Power%20Query%20value%20with%20leading%20space%20cannot%20covert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2670779%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20I%20am%20using%20the%20second%20variant%20of%20the%20code.%20I%20am%20now%20getting%20an%20%22Index%20was%20outside%20the%20bounds%20of%20the%20array.%22%20Is%20there%20a%20way%20to%20validate%20that%20the%20index%20list%20has%20reached%20its%20max%20before%20adding%20%221%22%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Deirdre1720 

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

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

@Sergei Baklan 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"?
Hi,
Add statement try after each and otherwise null before last )
Artur

@Deirdre1720 

If I remember the logic correctly latest in the group record always has Original Contract, all other are null.  With that you may modify the script as

    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    rows = Table.RowCount(#"Added Index"),
    // be sure Index starts from zero or change formula
    #"Added Custom" = Table.AddColumn(
        #"Added Index",
        "Custom",
        each
            if  [Index] < rows-1 and 
                #"Added Index"[Order Group Primary Number]{[Index] + 1} =
                #"Added Index"[Order Group Primary Number]{[Index]}
            then null
            else [Original Contract]
    )

Here we get number of rows in the table and check or we on last record or not.

@Sergei Baklan Thank you! The updated code takes more than 10 minutes, so I eventually kill it. I'll keep working with it. Thanks for all your help!
@artup720 Thanks! I am not successful in adding the try statement. The result is that it is always in error. I'm still playing with it.

@Deirdre1720 

Power Query is optimized to work with tables, not with lists and formulas in transformation. That's only to play with different variants.

You may try

- add Index to source

- reference this query, remove duplicates on "Order Group Primary Number" and Custom column with duplicating of "Original Contract"

- reference source with index again, here add Custom column with null in each record

- append latest two one to another, 

sort ascending on Primary Number and descending on Index,

add another Index to fix table in memory,

remove duplicates on first Index,

remove unused columns

result is like this

image.png

Please check in attached file.

@Deirdre1720  The syntax to use try & otherwise in the example from @Sergei Baklan could look like this:

 

 #"Added Custom" = Table.AddColumn(
        #"Added Index",
        "Custom",
        each
            if try  #"Added Index"[Order Group Primary Number]{[Index] + 1} =
                #"Added Index"[Order Group Primary Number]{[Index]}
            then null
            else [Original Contract] otherwise null
    )

 

In this case if [Index]+1 is greater then max of field Index Power Query will throw an error and will got to the code after otherwise statement an execute it (set null as value).

I also had problems with long running code

 

#"Added Index" = Table.AddIndexColumn(#"Inserted Merged Column", "UID", 0, 1, Int64.Type),
	Klucz = List.Buffer(#"Added Index"[Klucz]),
    gr = List.Buffer(#"Added Index"[WallThickness]),
    Custom1 = #"Added Index",
    #"Added Custom" = Table.AddColumn(Custom1, "WallThicknessFrom", each try if Klucz{[UID]-1}=Klucz{[UID]} then gr{[UID]-1} else -1 otherwise -1),

 

In the code above I had to compare values from current record with value from previous. I created two lists (Klucz = List.Buffer(#"Added Index"[Klucz]), AND gr = List.Buffer(#"Added Index"[WallThickness]),)

with List.Buffer statement. This keeps list in memory and query works really fast. As you can see here I'm also using try and otherwise statement.

Regards,

 

Artur