Split column by delimiter

[Anonymous]

Hi I would like to confirm how to split multiple columns by delimiter.

The requirement I want to realise is as below.

Requirement:

[danextian · Accepted solution]

Hi @Anonymous ,

Please try the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1QMFTSUXLMy0uMyXNJLMtMickLLi1OzAMKOpUWZ+alFhcDJVITczLz0oFSGfkFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Name", each Text.Split([Column2], "#(lf)") ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Department", each Text.Split([Column3],"#(lf)")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Zip({[Name],[Department]})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Text.Combine([Custom], "_")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Column2", "Column3", "Name", "Department", "Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name", "Department"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name", type text}, {"Department", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Group"}})
in
    #"Renamed Columns"

[amitchandak]

@Anonymous , Thhse are steps after data has been added, My column names are subject and marks and separator is comma (,)

added a new column and then expanded and deleted other columns

#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Zip", each let

_break = List.Zip( {Text.Split([Subject],","), Text.Split([Marks],",")} ),
_Trans = List.Transform(_break, each Record.FromList(_, {"Subject", "Marks"}))
in
_Trans),
#"Expanded Zip" = Table.ExpandListColumn(#"Added Custom", "Zip"),
#"Expanded Zip1" = Table.ExpandRecordColumn(#"Expanded Zip", "Zip", {"Subject", "Marks"}, {"Zip.Subject", "Zip.Marks"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Zip1",{"Subject", "Marks"})