Split string with delimiters in two columns and align them based on their sequence

[Anonymous]

hi Experts, 

my data is like this: 

ID	Name	Email
1234	['Johny Washer', 'Polly Dryer', 'Molly Windows']	['JohnyW@abc.com', 'DryerP@xyz.com', 'MollyW@yahoo.com']
6453	['Mickey Mouse']	['MouseM@Disney.com']
5678	['Star Trekky', 'War Starry']	['Trek@gmail.com', 'Starry@junk.com']

Expected outcome is: 

ID	Name	Email
1234	Johny Washer	JohnyW@abc.com
1234	Polly Dryer	DryerP@xyz.com
1234	Molly Windows	MollyW@yahoo.com
6453	Mickey Mouse	MouseM@Disney.com
5678	Star Trekky	Trek@gmail.com
5678	War Starry	Starry@junk.com

If I use split column using comma delimiter on each of the Name and Email columns, it splits correctly in to rows but it creates redundant rows for all combination. For example, Johny Washer gets aligned with JohnyW@abc.com as well as DryperP@xyz.com and MollyW@yahoo.com in 3 distinct rows. What I need is a split by delimiter and then aligning the first entry in Name column with the first entry in Email column and so on. The ID column should get repeated like shown in the expected outcome. 

I also need to do this each time I refresh data. So a manual right clicking for split column won't work. Is there any way to write a DAX for this so that the solution is automated and scalable? 

Thank you! 

[Anonymous]

Thank you.. Will test this out but what is the long text string in that code (binary.fromtext ?)

Appreciate help! 

[gmsamborn]

Hi @Anonymous 

That long string is how data from "Enter Data" is kept.  Just ignore it.

[Anonymous]

I couldn't figure out what that long test was and not sure what to replace it with or what a clean code would look like. I have not used "let" logic before. @amitchandak if you could attach the workbook, that would be awesome.

Just as an intermin solution, here's what I did. 

First split the concatenated strings using comma as the delimited and used substitute to remove [ and quotes. Used Pathitem(Substitute function and had 8 distinct columns for names and emails each. Concatenated them again to have 1:1 match with name and email. Hence, got 8 distinct columns for Name-Email1, Name-Email2 etc. 

Then created a new table and used UNION(SUMMARIZE to have ID in the first column and Name-Email in the second column. Then split the Name and Email into 2 distinct columns. I tested with changing values in the underlying CSV file and it works well. The problem is that it creates blank rows in between.. maybe I can setup a filter to remove the blanks. 

However, I would still prefer to use @amitchandak 's method since it will get rid of multiple steps. Thanks! 

[amitchandak]

@Anonymous , 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"