Forum Discussion
Using Substring to modify column data....Is it possible in Power BI?
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'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.
4 Replies
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.
Thank you both for the solution. I ended up creating new column and it's working as needed.
Thanks again for quick reply!
-B
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!
