Remove first 2 characters from a column value

[Namoh]

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.

Any idea how to do this?

Old	New
2018	18
2021	21
Something	mething

Once I have this formula, I can combvine it with my concatenate.

[Fowmy]

@Namoh 


Add this as a new custom column in Power Query.

Text.RemoveRange(Text.From([Old]),0,2)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn 

[Namoh]

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

[Tahreem24]

Try with MID:

= MID(Table[Old], 3,LEN(Table[Old]))

[enmanuel]

Excellent function. It's works.

[Anonymous]

you could also use the MID function 

MID('Table'[Old],3,50)

https://docs.microsoft.com/en-us/dax/mid-function-dax

[enmanuel]

Excellent function. It's works.

[az38]

@Namoh

New = RIGHT([Old], 2)

[Namoh]

I get the message RIGHT is not recognised

[Anonymous]

@Namoh Did my suggestion of using the MID function work for you? 

[Namoh]

No MID is also not recognised.

Are RIGHT and MID DAX functions?

As stated I'm in PQE.

[Anand24]

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 !!!

[Namoh]

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 · Accepted solution]

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]

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?

[JohnSalt]

I know this is an old post but a mathematical solution to getting the last two digits might be to rounddown and subtract from the original value:

        'Table'[Old]-Number.RoundDown('Table'[Old],-2)

[Anonymous]

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

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

[Anonymous]

Try the following 

= Text.Range([old],2)

[Namoh]

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