Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Substring from an exited column

I hav a colum productName, how can I add new column Prodcutnew wich I do a substring from position4?

 

For example

PoductName: 01-Auto I need to dispaly Auto

Need Help

4 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi, there is an "Extract" option on the menu in query editor to extract with ranges. You can use that to get a formula like this:

    = Table.AddColumn(#"Last Step", "Text Range Column", each Text.Middle([ColumnName], 3, 10), type text)

    The 3 is the characters to avoid from the beginning and the 10 is the number of chars to consider on the rest of the string.

    You can add that step in a blank one or search for "Extract" option under "Add Column" tab in query editor.

     

    Regards, 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      ibarrau 

      How can I put "10" total number of caracter? because I don't have the same total number of caracter for each product.

      • ibarrau's avatar
        ibarrau
        Super User
        6 years ago

        Consider adding the max number of chars that can be found on the column. If the column has less it won't matter because it will find the first, let's say 2, and extract the value. The 10 was an example.

         

        Regards,

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use two ways to meet your requirement.

     

    1. In Power Query, we can use the split column function.

     

     

    2. Or we can create a calculate column to split it.

     

    Prodcutnew = 
var len__ = LEN('Table'[Product])
var location__ = FIND("-",'Table'[Product],1,0)
return
RIGHT('Table'[Product],len__ - location__)

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.