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 

 

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.