Forum Discussion

Namoh's avatar
Namoh
Post Partisan
6 years ago
Solved

Remove first 2 characters from a column value

Hi.   I need to combine a few things, in PQE, in one new column. I can do this via CONCATENATE. Part of this combination is a value in a column, but I need to remove the first 2 characters.   A...
Need Help
  • Anand24's avatar
    Anand24
    6 years ago

    From PQE, you can perform the below for having last 2 digits of Number column:
    1. Duplicate the Number column

    2. Perform below operations from Home ->Transform section at top:

    Split Column >> By Number of Characters

     

    3. Modify with below:

    Number of Characters = 2

    Split = Once, as far right as possible

     

    4. Remove unwanted split column and rename new column

     

    Alternatively, you can use below formula in Advanced Editor:
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Number", "Duplicated Number"),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Duplicated Number", type text}}, "en-IN"), "Duplicated Number", Splitter.SplitTextByPositions({0, 2}, true), {"Duplicated Number", "New Number"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Duplicated Number"})

     

    Don't forget to give thumbs up and accept this as a solution if it helped you !!!

Namoh's avatar
Namoh
Post Partisan
6 years ago

No MID is also not recognised.

Are RIGHT and MID DAX functions?

As stated I'm in PQE.

Namoh's avatar
Namoh
Post Partisan
6 years ago

I might add that my first post was not completely correct.

My data doesn't contain text, only numbers.

So I need to find a PQE function with which I can show the last 2 digits of a number

  • Anand24's avatar
    Anand24
    Super User
    6 years ago

    From PQE, you can perform the below for having last 2 digits of Number column:
    1. Duplicate the Number column

    2. Perform below operations from Home ->Transform section at top:

    Split Column >> By Number of Characters

     

    3. Modify with below:

    Number of Characters = 2

    Split = Once, as far right as possible

     

    4. Remove unwanted split column and rename new column

     

    Alternatively, you can use below formula in Advanced Editor:
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Number", "Duplicated Number"),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Duplicated Number", type text}}, "en-IN"), "Duplicated Number", Splitter.SplitTextByPositions({0, 2}, true), {"Duplicated Number", "New Number"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Duplicated Number"})

     

    Don't forget to give thumbs up and accept this as a solution if it helped you !!!

    • Namoh's avatar
      Namoh
      Post Partisan
      6 years ago

      This worked, thanks.

       

      But isn't there a simple statement that can do this with numbers, just like the statements that are there for text?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Namoh to be fair, you did give us an alphanumeric example. Try the following. 

        = Text.Range(Number.ToText([Old]),2)

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    Try the following 

     

    = Text.Range([old],2)

     

     

    • Namoh's avatar
      Namoh
      Post Partisan
      6 years ago

      Nope, this didn't work, probably because it's not text but a number.