Forum Discussion
split columns with with multiple spaces between fields
Hey Team, We need to split text which looks like this to columns: Name= John Doe Date=2021-2-3 Address= New York Name= Sam Doe Date=2021-2-3 Address= City= Paris Is there a way to parse ...
This also can be done.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTbVV8MrPyFNwyU9VcEksSbU1MjAy1DXSNVZwTEkpSi0utlXwSy1XiMwvylaK1YFpCU7MxafDObOk0lYhILEos1gpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({"Name= "," Date=", " Address= "}, QuoteStyle.Csv), {"Column1","Name", "Date", "City"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","City= ","",Replacer.ReplaceText,{"City"})
in
#"Replaced Value"It has variable column names.