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)

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

 

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