Using Substring to modify column data....Is it possible in Power BI?

[Audi_B]

Hello all, I have one column in a list showing all accounts with a prefix on all account numbers of "00000". So each of these account numbers show like this: "00000123456789".

I know in typical SQL, I can use a simple:  substring([account_num], 6,9), but is there a way to modify this in Power BI using DAX, or any other solution to this?

Thank you!

-B

[Sean · Accepted solution]

@Audi_B

Try this in the Query Editor

Actually you want to select Last Characters

[Greg_Deckler · Accepted solution]

@Sean's solution will work for M code. 

In DAX, you could create a new column with the following formula:

If all account numbers are the same length:

AcctNum = RIGHT([Account Number],9)

If not:

AcctNum = MID([Account Number,6,LEN([Account Number]) - 5)

You may have to adjust start number and how many characters you are removing from what LEN returns, but general gist is there.

[Audi_B]

Thank you both for the solution. I ended up creating new column and it's working as needed.

Thanks again for quick reply!

-B

[Sean]

If you want to keep the original column - you can hit Duplicate Column first and then Rename and Transform the duplicate so you have access to both columns for reports.

EDIT: You never actually permanently Transform your original data even if you don't duplicate the Column.

You can go in the Query Settings => Applied Steps and just delete that step!

[Sean · Accepted solution]

@Audi_B

Try this in the Query Editor

Actually you want to select Last Characters