Forum Discussion
Anonymous
2 years agoNot applicable
Split string with delimiters in two columns and align them based on their sequence
hi Experts, my data is like this: ID Name Email 1234 ['Johny Washer', 'Polly Dryer', 'Molly Windows'] ['[email protected]', '[email protected]', '[email protected]'] 6453 ['Mickey Mouse'] ...
amitchandak
Super User
2 years agoAnonymous , Try this code in a blank query in Power Query (Transform Data)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY+9CsIwFEZfJWQuUu2fY4ZOQqCgkCFmiDXYmDaBxKLXp9e2Q2k7Bb5z7/luOMf7Q5LiCPNrH8eH4uQaC4jJ0Cg/JRGa3sq1LaDSwxrQETBt7+4dpkishIzIW72rXbfcHGUV+cB3y0YrIyAb52YqsIg4ztMsmRuoro0CRF0f1KZ+TCkpdbAK1p4sL47z6PklPbp4ZQwsT2H/fIAeNvphnDw6qdvtD6YV8uytWRSLHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Email = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Email", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"Name", "Email"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Name", "Email"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each List.Zip({Text.Split([Name], ","), Text.Split([Email], ",")})),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Name", "Email"})
in
#"Removed Columns"