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

I get the message RIGHT is not recognised

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Namoh Did my suggestion of using the MID function work for 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 !!!

    • Anand24's avatar
      Anand24
      Super User
      6 years ago

      In PQE(Power Query Editor), follow below steps:
      1. Right click 'Old' columnname

      2. Click on 'Split Column'

      3. Click on 'By Positions...' (3rd option)

      4. Type 2 in positions text field

      5. Click OK

      6. Rename the column to "New"

       

      If you want to keep both 'Old' as well as 'New' columns, make sure you duplicate the column first and then perform above steps on duplicated column.

       

       

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