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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
NurAmirah_MY
Regular Visitor

Split by comma into row

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

7 REPLIES 7
PijushRoy
Super User
Super User

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

PijushRoy_1-1703880756034.png

 

Step 2 - Replace value

PijushRoy_2-1703880840591.png

 

Then Split

PijushRoy_3-1703880896532.png

 

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




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





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.

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-... 





Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





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_IDOver the 12 months, what are the topics you would like to learn? Select all that apply
test01Foreign exchange,Pension
test02Life Insurance, Takaful and Hibah,Pension
test03Pension,Bonds & Loan
test04Bonds & Loans,Foreign exchange,Pension,Life Insurance, Takaful and Hibah

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.

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

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors