Forum Discussion

Deirdre1720's avatar
Deirdre1720
Copper Contributor
Aug 09, 2021

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]...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 09, 2021

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's avatar
Deirdre1720
Copper Contributor
Aug 20, 2021
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"?
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 21, 2021

    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.

    • Deirdre1720's avatar
      Deirdre1720
      Copper Contributor
      Aug 23, 2021
      SergeiBaklan 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!
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 23, 2021

        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

        Please check in attached file.

  • artup720's avatar
    artup720
    Copper Contributor
    Aug 20, 2021
    Hi,
    Add statement try after each and otherwise null before last )
    Artur
    • Deirdre1720's avatar
      Deirdre1720
      Copper Contributor
      Aug 23, 2021
      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.
      • artup720's avatar
        artup720
        Copper Contributor
        Aug 24, 2021

        Deirdre1720  The syntax to use try & otherwise in the example from SergeiBaklan 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