Forum Discussion

Audi_B's avatar
Audi_B
Frequent Visitor
10 years ago
Solved

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 S...
Need Help
  • Sean's avatar
    Sean
    10 years ago

    Audi_B

    Try this in the Query Editor

     

    Actually you want to select Last Characters

  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
Audi_B
Frequent Visitor
10 years ago

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's avatar
Sean
Community Champion
10 years ago

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!