Forum Discussion
Split column by delimiter
Hi I would like to confirm how to split multiple columns by delimiter.
The requirement I want to realise is as below.
Requirement:
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"
2 Replies
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"})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"
