Forum Discussion
How to separate Text with commas and different Text ordered
Hi Folks,
I have the following issue, and I am not sure how should I fix it. What I am trying to do is basically separate text by commas from a single column I tried to use the Add Column > Extract option, but since there is a lot of text and it does not follow a patron I am struggling on it.
I have the following text as example:
And sometimes the Column A the patron is not the same for example it could be only Effort,other and I need to have all the efforts on the same column, and all the Others in the same column.
Basically, I am trying to classify these text. I would really appreciate your help, if anyone can suggest me any idea?
Thank you so much.
Hi, Anonymous
Based on your description, I assume that you want to align the split text to the right. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may apply the following transformations in the 'Advanced Editor'.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s7LL89JTUlPTUzKSdVxzS1ILMlILclM1nFNS8tMzkzNK9FxzkksyiypBInkF5Xo+AMVFCnF6kQrkaiasBLsErEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column A", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6"}), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each List.RemoveNulls( Record.ToList(_) )), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Count([Custom])), Custom1 = Table.TransformColumns(#"Added Custom1",{"Custom",each let l=List.Max(#"Added Custom1"[Custom.1]), res=List.Combine({ List.Repeat({null},l-List.Count(_)),_}) in res }), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6", "Custom.1"}), Custom2 = Table.TransformColumns(#"Removed Columns",{"Custom",each Record.FromList(_,Table.ColumnNames(#"Split Column by Delimiter") )}), #"Expanded Custom" = Table.ExpandRecordColumn(Custom2, "Custom", {"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6"}, {"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6"}) in #"Expanded Custom"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hello Anonymous ,
I don't understand your request.Please check out these tips to get you questions answered quickly:
help how to get your questions answered quickly - Microsoft Power BI Community
Hi, Anonymous
Based on your description, I assume that you want to align the split text to the right. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may apply the following transformations in the 'Advanced Editor'.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s7LL89JTUlPTUzKSdVxzS1ILMlILclM1nFNS8tMzkzNK9FxzkksyiypBInkF5Xo+AMVFCnF6kQrkaiasBLsErEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column A", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6"}), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each List.RemoveNulls( Record.ToList(_) )), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Count([Custom])), Custom1 = Table.TransformColumns(#"Added Custom1",{"Custom",each let l=List.Max(#"Added Custom1"[Custom.1]), res=List.Combine({ List.Repeat({null},l-List.Count(_)),_}) in res }), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6", "Custom.1"}), Custom2 = Table.TransformColumns(#"Removed Columns",{"Custom",each Record.FromList(_,Table.ColumnNames(#"Split Column by Delimiter") )}), #"Expanded Custom" = Table.ExpandRecordColumn(Custom2, "Custom", {"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6"}, {"Column A.1", "Column A.2", "Column A.3", "Column A.4", "Column A.5", "Column A.6"}) in #"Expanded Custom"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
