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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
datanau001
Helper III
Helper III

Split lines based on the cell content

Dear all,

I would like to ask for your support with the following.

My original table has some fields containing free text and in these fields, I can have some #tags, like #1234,#0ABC,…

 

In this example columns, Summary and Comments are showing some of the tags that I created.

So we could assume that separating the column by character could help, but in a real case, these #tags will come in a free text field (words, numbers, in different lines,...), and then it won't work.

Another possibility would be working with Search or Contains, but this might not help because I can have more than one #tag in a cell.

See below:

Ticket Summary Comments
AIC200604686  #1234,#1256  #1234,#1256
AIC200604611  #6987  #6987
AIC200604482  #0001,#0002  #0002
AIC200604442  #0ABC,#0BCDA  #0ABC,#0BCDA,
AIC200604436  #NAME,#6987,#1234  AAAAA
AIC200604397  #ERROR,#FIX123 #ERROR,#FIX1234

 

What I need to is to split the lines from the original table based on the #TAGS that it contains.

And then show the matches of these #TAGS in a new column.

 

Here is the example of what I want to achieve:

Ticket

  Summary

Comments

  TAG

AIC200604686

  #1234 #1256

  #1234 #1256

  #1234

AIC200604686

  #1234,#1256

  #1234,#1256

  #1256

 

- the first ticket became 2 lines with a column which is showing the #TAG.

 

Here is the simulation on the whole table where pratically all tickets would be splited:

 

Ticket  Summary  Comments  TAG 
AIC200604686  #1234 #1256  #1234 #1256  #1234
AIC200604686  #1234,#1256  #1234,#1256  #1256
AIC200604611  #6987  #6987  #6987
AIC200604482  #0001,#0002  #0002  #0001
AIC200604482  #0001,#0002  #0002  #0002
AIC200604442  #0ABC,#0BCDA  #0ABC,#0BCDA,  #0ABC
AIC200604442    #0ABC,#0BCDA  #0ABC,#0BCDA,  #0BCDA
AIC200604436  #NAME #6987, #1234  AAAAA #BBBBB  #NAME
AIC200604436  #NAME,#6987,#1234  AAAAA #BBBBB  #6987
AIC200604436  #NAME,#6987,#1234  AAAAA #BBBBB  #1234
AIC200604436  #NAME #6987, #1234  AAAAA #BBBBB  #BBBBB
AIC200604397  #ERROR,#FIX123  #ERROR,#FIX1234  #FIX123
AIC200604397  #ERROR,#FIX123  #ERROR,#FIX1234  #ERROR
AIC200604397  #ERROR,#FIX123  #ERROR,#FIX1234  #FIX1234

 

Would you please suggest how can I achieve this.

 

Regards

Datanaut

 

1 ACCEPTED SOLUTION

Hi @datanau001  ,

 

According to your data, you could split and unpivot columns in powerbi query by the following steps:

Step 1: Duplicate the Summary and Comments columns

duplicate.jpg

Step2: Split duplicated column

spilt.jpgspilt2.jpg

Step3: Unpivot column(Use shift to select split columns)

unpivot.jpg

Step4: Remove unnecessary columns

remove.jpg

Step5: Conditional columns and remove duplicate rows

 
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5BDoQgDEWvYnDbRYEOg8uCTuJCJ2FlYrj/NQTUKNpF0/6+n/51FTx6hWiQjDUCRNO0UmmC1D/vPcLdIOUOmM5+b1MFkVX7CREl5H7t6oHSeWLnE+p8zy8BHh59hJx5GqD8hxK4qJyrNujuiDqE8A/Q/sYl4VmqFRIxbg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #" Summary" = _t, #" Comments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", type text}, {" Summary", type text}, {" Comments", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", " Summary", " Summary - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", " Comments", " Comments - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", " Summary - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" Summary - Copy.1", " Summary - Copy.2", " Summary - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{" Summary - Copy.1", type text}, {" Summary - Copy.2", type text}, {" Summary - Copy.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", " Comments - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" Comments - Copy.1", " Comments - Copy.2", " Comments - Copy.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{" Comments - Copy.1", type text}, {" Comments - Copy.2", type text}, {" Comments - Copy.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{" Comments - Copy.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Ticket", " Summary", " Comments"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1"," ","",Replacer.ReplaceText,{"Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.StartsWith([Value], "#") then [Value] else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Ascending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows",1),
    #"Sorted Rows1" = Table.Sort(#"Removed Top Rows",{{"Ticket", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows1", {"Custom"})

In addition: Because this is the cleaned data, the above steps are solutions for the cleaned data. Or use "," replace the value that you don't want then split it.

If it does not make sense, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@datanau001 , You have an option in the power query to split the column.

https://www.sumproduct.com/blog/article/power-bi-tips/power-bi-new-transform-split-column-by-positio...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hello @amitchandak

I'm afraid it could not be an option. In this example, I'm just showing the "#TAGS" (it is clean).

But these columns in a real case will be containing the tags inside a free text. 

Hi @datanau001  ,

 

According to your data, you could split and unpivot columns in powerbi query by the following steps:

Step 1: Duplicate the Summary and Comments columns

duplicate.jpg

Step2: Split duplicated column

spilt.jpgspilt2.jpg

Step3: Unpivot column(Use shift to select split columns)

unpivot.jpg

Step4: Remove unnecessary columns

remove.jpg

Step5: Conditional columns and remove duplicate rows

 
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5BDoQgDEWvYnDbRYEOg8uCTuJCJ2FlYrj/NQTUKNpF0/6+n/51FTx6hWiQjDUCRNO0UmmC1D/vPcLdIOUOmM5+b1MFkVX7CREl5H7t6oHSeWLnE+p8zy8BHh59hJx5GqD8hxK4qJyrNujuiDqE8A/Q/sYl4VmqFRIxbg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, #" Summary" = _t, #" Comments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", type text}, {" Summary", type text}, {" Comments", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", " Summary", " Summary - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", " Comments", " Comments - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", " Summary - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" Summary - Copy.1", " Summary - Copy.2", " Summary - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{" Summary - Copy.1", type text}, {" Summary - Copy.2", type text}, {" Summary - Copy.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", " Comments - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {" Comments - Copy.1", " Comments - Copy.2", " Comments - Copy.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{" Comments - Copy.1", type text}, {" Comments - Copy.2", type text}, {" Comments - Copy.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{" Comments - Copy.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Ticket", " Summary", " Comments"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1"," ","",Replacer.ReplaceText,{"Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.StartsWith([Value], "#") then [Value] else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Ascending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows",1),
    #"Sorted Rows1" = Table.Sort(#"Removed Top Rows",{{"Ticket", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows1", {"Custom"})

In addition: Because this is the cleaned data, the above steps are solutions for the cleaned data. Or use "," replace the value that you don't want then split it.

If it does not make sense, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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