is it possible to Split column based on condition?

[Anonymous]

Hi all

I have Filed Like "Input". how can I get data like "Output"

how can I split Input Column based on the condition  

Input	Out Put
<div class="ExternalClassF9656CEB16D34E9A88ADC81FA9097FF9">He is good in communication </div>	He is good in communication
<div class="ExternalClassF43F496543AD123E4FD0E">Not confident in speeking</div>	Not confident in speeking
<div class="ExternalClassF9A2E569C6E64C7B890A77B0FDB9787E">4</div>	4
<div class="ExternalClassF9D36624B900D080287">Communication is good, Even his listening communication is good.</div>	Communication is good, Even his listening communication is good.
<div class="ExternalClassFA1CEED42BE74">Less communication knowledge.</div>	Less communication knowledge.
He is good in communication	He is good in communication
Communication is good	Communication is good
Ok	Ok

[Greg_Deckler · Accepted solution]

See if this works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZLLboMwEEV/xWKNWgOOH4sujB/qomo/ILCIwKUWxFQ1Sfv5dUKo1IgkSNlYGvn6zpnrWa+jYgdhVtV2D6pu4/1TEamfwXy5TScOtWZ4hYXKEywzpBinlEtBE80ZZERrVowG5tkA60HT9zWwDlT9drtzttoMtndgbPEYeozaKI6uyKMyvk2FMo0CGcq4TNJMIS2hmlBe+yE4undbGzcc7P2nMa11zQzHRe0iCsZTtcJMYIWRIDllkBOSQy1zRij5A0IzjdGyBjLDOEU5g1BCClNKJk/xL+FTljFQe+PARyg76wfjwiBnf3FSPswg3Wu5aCKeCKUkSnNF0DTLi/H+zLN1/Xdn6sbMgV7VHymurdft5ZsN4lJAxxdvbbgOR1n+Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Input = _t, #"Out Put" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}, {"Out Put", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Input", Splitter.SplitTextByEachDelimiter({""">"}, QuoteStyle.None, true), {"Input.1", "Input.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Input.1", type text}, {"Input.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Input.2", Splitter.SplitTextByEachDelimiter({"</"}, QuoteStyle.Csv, true), {"Input.2.1", "Input.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Input.2.1", type text}, {"Input.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Input.2.2", "Out Put"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [Input.2.1] = null then [Input.1] else [Input.2.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Input.1", "Input.2.1"})
in
    #"Removed Columns1"