Split Text within Same Column

[RAHULBANDI]

Hi

I have following data-set 

DATA

Reduction of Capital

Right issue of equity shares

Consolidation of shares

Exchange of share Certificate

Right issue of equity shares

  EXPECTED -OUTPUT:

DATA

Reduction of Capital

Right issue 

Consolidation of shares

Exchange of share Certificate

Right issue 

I want to split the "Right Issue" within same column

[Vijay_A_Verma · Accepted solution]

Use this where Source need to be replaced with your previous step

= Table.ReplaceValue(Source,each [DATA],each Text.BeforeDelimiter([DATA], " of equity shares"),Replacer.ReplaceValue,{"DATA"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcxLCoAwDATQqwTX3qJ4AbfFRaixDZRGTQp6ez+guHQ7b2a8b3oaazCWAjKBw5kNczO0J3BMBqxa6SJaKtsOmnAlvQtOikrmEZ/1x7otJCyR3hgcrcYTBzT6cT8c", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATA = _t]),
    Custom1 = Table.ReplaceValue(Source,each [DATA],each Text.BeforeDelimiter([DATA], " of equity shares"),Replacer.ReplaceValue,{"DATA"})
in
    Custom1

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

[wdx223_Daniel · Accepted solution]

NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each if Text.StartsWith(_,"Right issue") then "Right issue" else _})

[mahenkj2]

Hi @RAHULBANDI ,

One way is to this is:

Hope it helps. 

[AlexisOlson]

The best solution depends on how general the solution needs to be and what other values exist in the DATA column.

With the given example, you can simply Replace "Right issue of equity shares" with "Right issue".

[Vijay_A_Verma · Accepted solution]

Use this where Source need to be replaced with your previous step

= Table.ReplaceValue(Source,each [DATA],each Text.BeforeDelimiter([DATA], " of equity shares"),Replacer.ReplaceValue,{"DATA"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcxLCoAwDATQqwTX3qJ4AbfFRaixDZRGTQp6ez+guHQ7b2a8b3oaazCWAjKBw5kNczO0J3BMBqxa6SJaKtsOmnAlvQtOikrmEZ/1x7otJCyR3hgcrcYTBzT6cT8c", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATA = _t]),
    Custom1 = Table.ReplaceValue(Source,each [DATA],each Text.BeforeDelimiter([DATA], " of equity shares"),Replacer.ReplaceValue,{"DATA"})
in
    Custom1

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data