Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Remove trailing 2 characters from end string

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

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Anonymous ,

    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

    Remove trailing 2 characters from end string.pbix34 KB

6 Replies

  • slounsbury's avatar
    slounsbury
    Helper II
    7 years ago

    Hi Anonymous 

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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
)