cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions
Highlighted
stretcharm Senior Member
Senior Member

Re: Power Query - Remove last two characters from a column

 

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"})

 

 

Interkoubess Established Member
Established Member

Re: Power Query - Remove last two characters from a column

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

5 REPLIES 5
Highlighted
stretcharm Senior Member
Senior Member

Re: Power Query - Remove last two characters from a column

 

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"})

 

 

Super User
Super User

Re: Power Query - Remove last two characters from a column

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

Interkoubess Established Member
Established Member

Re: Power Query - Remove last two characters from a column

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

Re: Power Query - Remove last two characters from a column

Thank you for multiple helpfuö answers!
suzannek Frequent Visitor
Frequent Visitor

Re: Power Query - Remove last two characters from a column

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