Remove trailing 2 characters from end string

[kpathiakis]

Hello,

I am rather new to the M langauge and need some help. 

I have a column with strings that are either BFD###### or BF###### . I am looking to remove the last 2 characters from this string, so each will either be BFD#### or BF####. I have tried other methods but I end up with only 1 character being removed from the BFD strings and zero characters being removed from the BF strings.

Thank you for your help

[Anonymous]

If the end goal is to have each be 7 characters long, can use the following as a new column in Power Query:

Text.Start( 
[Column1],
7
)

[kpathiakis]

Thanks for the help! In the end, it will be either 6 or 7 characters. I am basically looking to remove the 2 end characters, no matter how long the string is 

[slounsbury]

@kpathiakis 

This request looks like it may solve your issue if you haven't got it already.

https://community.powerbi.com/t5/Desktop/Power-Query-Remove-last-two-characters-from-a-column/td-p/3...

Scott

[kpathiakis]

@slounsbury 

Thanks Scott. I actually had tried that. But what it is doing is either removing 1 or 2 characters. Take a look at the outcome when I try to split the columns. The left is the before, the right is the after.  ;

[v-danhe-msft · Accepted solution]

Hi @kpathiakis ,

Based on my test, you could refer to below steps:

In power query:

Add a custom column and enter below code:

Text.Start([Data],Text.Length([Data])-2)

Result:

In dax, you could refer to below formula:

Column = LEFT('Table1 (2)'[Data],LEN('Table1 (2)'[Data])-2)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[slounsbury]

Hi @kpathiakis 

One thing you could try is going into the Power Query Editor and split the column after 7 characters and then deleting the column it creates from the split which would leave you with the column you are looking for. You can always go through and remove the applied steps in the editor if you need to revert it or are not satisfied with the outcome.

Scott