Split by comma into row

[NurAmirah_MY]

Hi,

I have set of data and want to split by comma into row. However, some of of the text that has comma I want to keep it as it is, which means no need to split. 

eg: Data I have --> I enjoy swimming,I enjoy reading, I enjoy hiking, exploring and walking. 
Data I want it to be by row:

I enjoy swimming

I enjoy reading

I enjoy hiking, exploring and walking

How can I have such data? Thanks

[PijushRoy]

Hi @NurAmirah_MY 
You needs to two times replace value as space are not consistent in text
Step 1 Replace Value

Step 2 - Replace value

Then Split

https://drive.google.com/file/d/1yV31w4lgjBBkFXymCQ9AV4yJM3Uk6OuB/view?usp=sharing

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS

Thanks
Pijush

[NurAmirah_MY]

Hi @PijushRoy 

Thanks for the solution. I have tried but the word with , that I want to keep still splitting. I have replave value of "," with "#" 2 times and split by row.

I'm not sure what went wrong. Let me repharse the example:

eg: Data I have --> Trust,Happiness, enjoyment and excited,pessimistic, optimistic 
Data I want it to be by row:

Trust

Happiness, enjoyment and excited

pessimistic 

optimistic

Does the same solutiom applied? Thanks.

[PijushRoy]

Hi @NurAmirah_MY 

Please share the sample data that you are using in excel format
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-... 

[NurAmirah_MY]

Hi @PijushRoy 

The questions asked in the survey are:

Over the 12 months, what are the topics you would like to learn? Select all that apply

• Foreign exchange
• Pension
• Life Insurance, Takaful and Hibah
• Bonds & Loans

Here are the sample data in Excel format. Let me know if this is sufficient for the testing. Thanks.

Resp_ID	Over the 12 months, what are the topics you would like to learn? Select all that apply
test01	Foreign exchange,Pension
test02	Life Insurance, Takaful and Hibah,Pension
test03	Pension,Bonds & Loan
test04	Bonds & Loans,Foreign exchange,Pension,Life Insurance, Takaful and Hibah

[spinfuzer]

You can try something like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSs1Mz1NIrUjOSMxLT9UJSM0rzszPU4rViVbyyUxLVfDMKy4tSsxLTtVRCEnMTkwrzVFIzEtR8MhMSsxAUQ1l6zjl56UUK8SUGhgYmSn45CfmFYOlMYV1cFmuQ9BipdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    options = {
        "Foreign exchange",
        "Pension",
        "Life Insurance, Takaful and Hibah",
        "Bonds & Loans"
    },
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
pos = List.RemoveMatchingItems(List.Accumulate(options, {}, (s,c) => s & {Text.PositionOf([Column1], c)}), {-1})
in Splitter.SplitTextByPositions(List.Sort(pos))([Column1])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded Custom",{{"Custom", each Text.TrimEnd(_,","), type text}})

in
    #"Trimmed Text"

I did an add column instead of transform column so I could check the output while testing.

[wdx223_Daniel]

what's the difference between the comma after "Happiness" and "pessimistic"?

[NurAmirah_MY]

The comma after "Happiness" has a space. The comma after "Pessimistic" don't has a space.