Forum Discussion

RAHULBANDI's avatar
RAHULBANDI
Helper II
4 years ago
Solved

Replace Existing Text

Hi  I have Following Data   NAME Amalgamation Annual Book Closure Bonus Issue Right Issue of Equity Shares Right Issue of Equity Shares with Warrants Right Issue of Fully Convertible Debentu...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    What if you try this?

     

    = Table.AddColumn(Origen, "Custom", each let splitNAME = Splitter.SplitTextByDelimiter("Right Issue", QuoteStyle.None)([NAME]) in Text.Combine({Text.Start([NAME], 11), Text.Middle(splitNAME{0}?, 11)}), type text)

mussaenda's avatar
mussaenda
Community Champion
4 years ago

Select your column > Transform > Extract > Before Delimiter > Advance Options 

Delimiter put space

Number of Delimitiers to skip put 1

  • RAHULBANDI's avatar
    RAHULBANDI
    Helper II
    4 years ago

    Hi 

    I have other rows in that data which are also getting splitted

     

    NAME

    Amalgamation                                                          Amalgamation   

    Annual Book Closure                                                Annual Book 

    Bonus Issue                                                                 Bonus Issue

    Right Issue of Equity Shares                                        Right Issue

    Right Issue of Equity Shares with Warrants                Right Issue

    Right Issue of Fully Convertible Debentures               Right Issue

    Exchange of Share Certificate                                     Excahnge of

     

    Im looking replace only text which starts with Right Issue

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      What if you try this?

       

      = Table.AddColumn(Origen, "Custom", each let splitNAME = Splitter.SplitTextByDelimiter("Right Issue", QuoteStyle.None)([NAME]) in Text.Combine({Text.Start([NAME], 11), Text.Middle(splitNAME{0}?, 11)}), type text)

      • RAHULBANDI's avatar
        RAHULBANDI
        Helper II
        4 years ago

        thq very much its working

    • mussaenda's avatar
      mussaenda
      Community Champion
      4 years ago

      try this

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hYyxCsJQDAB/JXT2J2xVcNXBoXRIIfYF0wTzEqV/r7Sr4HjHcX3f7GeUCWcMNm2G3VeoJgq0Zg/oxGo6rb41zQrnWnPjC08lNga7w/GZHAtcCzrVvwG8OQrc0B01fuanFFmgM32RB49CcKCRNHLdDx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Column1", each if Text.StartsWith(Text.Upper(_), "RIGHT ISSUE") then Text.BeforeDelimiter(_, " ", 1) else _, type text}})
in
    #"Extracted Text Before Delimiter"

       

      Same procedure, 

      Open Blank query, Advanced Editor, Select all delete and paste the code