Forum Discussion

Alex03260's avatar
Alex03260
Regular Visitor
3 years ago

Extract Text Based on Length of String

I have text in a column "Outline_Number" I want to extract some of the text based on the length of the string to a new column "Outline_Parent"

 

i.e. I want the whole string minus the last 2 characters, this would be easy in excel but i'm relatively new to BI.

 

"Outline_Number" = 1.1.2.2.4

 

Result

 

"Outline_Parent" = 1.1.2.2

 

Thanks 

 

Alex

 

Need Help

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    You may use this

    Text.Start([Outline_Number],Text.Length([Outline_Number])-2)

    If you have string less than 2 in length

    then

    try Text.Start([Outline_Number],Text.Length([Outline_Number])-2) otherwise null
    • Alex03260's avatar
      Alex03260
      Regular Visitor
      3 years ago

      The first version worked but as expected gives an error on short string, the 2nd version gives a syntax error on the "otherwise null"