Forum Discussion

Cyrilbrd's avatar
Cyrilbrd
Helper IV
6 years ago
Solved

Text.Remove

Given a series of model code such as:

BONH-P-M-16

CAN-BL-A17

88-R-AC-19

Said column also contain blanks.

New calculated column need to return the following:

BONH-P-M

CAN-BL-A

88-R-AC

Present formula used is:

= Table.AddColumn(framework_tblsalesitem, "model_code(", each Text.Trim(Text.Start(Text.Remove([product_name],{"0","1","2","3","4","5","6","7","8","9"}),Text.PositionOf(Text.Remove([product_name],{"0","1","2","3","4","5","6","7","8","9"}),"-",Occurrence.Last))))

 

Issue encountered:

All numbers are removed, yet only the last string shoudl be removed.

88-R-AC-19 gives -R-AC but we need to see 88-R-AC

All blanks should return a "no data" Maybe with TRY OTHERWISE? not sure of the proper syntax.

 

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi Cyrilbrd 

    if you want to do this via the UI, you add a custom column and paste in this formula:

     

    Text.TrimEnd([product_name], {"0".."9"} & {"-"})

     

     

9 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Cyrilbrd  

    there is an optional parameter in the Text.TrimEnd function that allows you to pass a list of characters that shall be removed from the end of the string:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL389AN0PXVNTRTitWJVnJ29NN18tF1NDQHc8GEhYVukK6js66hpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product_name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.TrimEnd([product_name], {"0".."9"} & {"-"}))
in
    #"Added Custom"

     

     

    • Cyrilbrd's avatar
      Cyrilbrd
      Helper IV
      6 years ago

      Thank you for your reply, did not try yet, would not have found this by myself either.

      Will this modify the Column Product_name or will it create as required a new Column Model_Code( ?

      What is that part doing exactly?

      Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL389AN0PXVNTRTitWJVnJ29NN18tF1NDQHc8GEhYVukK6js66hpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate))

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi Cyrilbrd ,

        this just creates sample data. (It is the resulting code when you enter data manually in Power BI).

        Replace it with the reference to your table.