How to separate Text with commas and different Text ordered

[Anonymous]

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. 

[v-alq-msft · Accepted solution]

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.

[ImkeF]

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