The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
Step2: Split duplicated column
Step3: Unpivot column(Use shift to select split columns)
Step4: Remove unnecessary columns
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.
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
Step2: Split duplicated column
Step3: Unpivot column(Use shift to select split columns)
Step4: Remove unnecessary columns
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.