Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

is it possible to Split column based on condition?

Hi all

 

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

 

how can I split Input Column based on the condition  

 

InputOut 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 communicationHe is good in communication
Communication is goodCommunication is good
OkOk
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

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"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Community Champion
Community Champion

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"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors