Forum Discussion

NurAmirah_MY's avatar
NurAmirah_MY
Regular Visitor
2 years ago

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 sw...
Need Help
NurAmirah_MY's avatar
NurAmirah_MY
Regular Visitor
2 years ago

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.

  • NurAmirah_MY's avatar
    NurAmirah_MY
    Regular Visitor
    2 years ago

    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
    • spinfuzer's avatar
      spinfuzer
      Solution Sage
      2 years ago

      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.