Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Namoh
Post Partisan
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?

OldNew
201818
202121
Somethingmething

 

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

 

 

 

1 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

1.PNG

 

3. Modify with below:

Number of Characters = 2

Split = Once, as far right as possible

2.PNG

 

4. Remove unwanted split column and rename new column

3.PNG

 

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

View solution in original post

18 REPLIES 18
Fowmy
Super User
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 🙂

YouTube, LinkedIn 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Tahreem24
Super User
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

Excellent function. It's works.

Anonymous
Not applicable

you could also use the MID function 

 

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

Karlos_0-1596274575918.png

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

 

 

Excellent function. It's works.

az38
Community Champion
Community Champion

@Namoh

New = RIGHT([Old], 2)

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

I get the message RIGHT is not recognised

 

Anonymous
Not applicable

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

No MID is also not recognised.

Are RIGHT and MID DAX functions?

As stated I'm in PQE.

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.

 

Community Solution.PNG

 

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

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

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

1.PNG

 

3. Modify with below:

Number of Characters = 2

Split = Once, as far right as possible

2.PNG

 

4. Remove unwanted split column and rename new column

3.PNG

 

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

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?

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)

 

Karlos_0-1596376875578.png

 

Anonymous
Not applicable

 

Try the following 

 

= Text.Range([old],2)

 

Karlos_0-1596287411001.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.