Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FatherTheWizard
Resolver I
Resolver I

Power Query - Remove last two characters from a column

Hi,

 

Simple question. How to remove last two characters from each field of the column, i.e. replicate Excel's LEFT("column";LEN(column)-2) in M language?

 

Thanks!

2 ACCEPTED SOLUTIONS
stretcharm
Memorable Member
Memorable Member

 

Use Split

 

Right mouse on col, select split, then by char by position. Enter 2 and pick once from right. You'll get 2 columns so delete the one you don't want.

 

The M /Power Query code looks like this.

 

= Table.SplitColumn(#"Removed Columns1", "Field", Splitter.SplitTextByPositions({0, 2}, true), {"Field.1", "Field.2"})

 

 

View solution in original post

Hi @FatherTheWizard,

 

As explained you can use this formula in M:

Text.Start([column];Text.Length([column])-2)

That means your column is a text and it is not empty or null otherwise you need to add a condition...

 

Hope it helps...

 

Ninter

View solution in original post

8 REPLIES 8
jthomson
Solution Sage
Solution Sage

Text.Length is the M equivalent to LEN, if you use that in combination with Text.RemoveRange you should be able to do what you want

Hi @FatherTheWizard,

 

As explained you can use this formula in M:

Text.Start([column];Text.Length([column])-2)

That means your column is a text and it is not empty or null otherwise you need to add a condition...

 

Hope it helps...

 

Ninter

@Interkoubess I stumbled across this post when trying to create a new column in power Query that remove the last digit of a string if it begins with 9.  

For example 9396920 would be 939692 but 2020010 would remain the same.  

I wrote this to identify if the column begins with 9.

Text.StartsWith([Custom],"9")

 

but when I try to conbine with the solution below, I get an error message.  

Text.Start([column];Text.Length([column])-2)

 

Help would be much appreciated.

For anyone who found this and wonders why its an error; The original user uses semicolons instead of commas for formulas - Replace ; with , and it will work.

Thank you for multiple helpfuö answers!
stretcharm
Memorable Member
Memorable Member

 

Use Split

 

Right mouse on col, select split, then by char by position. Enter 2 and pick once from right. You'll get 2 columns so delete the one you don't want.

 

The M /Power Query code looks like this.

 

= Table.SplitColumn(#"Removed Columns1", "Field", Splitter.SplitTextByPositions({0, 2}, true), {"Field.1", "Field.2"})

 

 

A bit clearer explanation:

 

inPower Query, use Split Column by Number of Characters. Enter 2 (or the number of characters you need to split) and pick "Once, as far right as possible".

 

Thank you for this answer. You saved me tons of time!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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