cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

## 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.

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.

1 ACCEPTED SOLUTION
Super User

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

18 REPLIES 18
Super User

@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 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Post Partisan

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

Super User

Try with MID:

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

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Frequent Visitor

Excellent function. It's works.

Anonymous
Not applicable

you could also use the MID function

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

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

Frequent Visitor

Excellent function. It's works.

Community Champion

@Namoh

``New = RIGHT([Old], 2)``

do not hesitate to give a kudo to useful posts and mark solutions as solution
Post Partisan

I get the message RIGHT is not recognised

Anonymous
Not applicable

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

Post Partisan

No MID is also not recognised.

Are RIGHT and MID DAX functions?

As stated I'm in PQE.

Super User

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

Post Partisan

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

Super User

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

Post Partisan

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?

Helper I

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
Not applicable

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

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

Anonymous
Not applicable

Try the following

``= Text.Range([old],2)``

Post Partisan

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.