Forum Discussion

Cyrilbrd's avatar
Cyrilbrd
Icon for Helper IV rankHelper 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...
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"} & {"-"})

     

     

Cyrilbrd's avatar
Cyrilbrd
Icon for Helper IV rankHelper 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
Icon for Community Champion rankCommunity 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.

 

 

  • Cyrilbrd's avatar
    Cyrilbrd
    Icon for Helper IV rankHelper IV
    6 years ago

    Would that be correct?

    let
  Source = Table.FromRows(framework_tblsalesitem), 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", "model_code(", each Text.TrimEnd([product_name], {"0".."9"} & {"-"}))
in
    #"Added Custom"

    How should I generate this column? Should I still use add column? 

     

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi Cyrilbrd ,

      if the code you've posted doesn't work, please post the error-message you see.

       

       

      • Cyrilbrd's avatar
        Cyrilbrd
        Icon for Helper IV rankHelper IV
        6 years ago

        Sorry but I feel you misunderstood my question.

        How do I apply your solution?

        Once in Power Query Editor, what shall I do? 

        Insert a Column?

        Thanks.