Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

hi Experts, 

my data is like this: 

IDNameEmail
1234['Johny Washer', 'Polly Dryer', 'Molly Windows']['[email protected]', '[email protected]', '[email protected]']
6453['Mickey Mouse']['[email protected]']
5678['Star Trekky', 'War Starry']['[email protected]', '[email protected]']

 

Expected outcome is: 

 

IDNameEmail
1234Johny Washer[email protected]
1234Polly Dryer[email protected]
1234Molly Windows[email protected]
6453Mickey Mouse[email protected]
5678Star Trekky[email protected]
5678War Starry[email protected]

 

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 [email protected] as well as [email protected] and [email protected] 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! 

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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"



     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    Appreciate help! 

     
    • gmsamborn's avatar
      gmsamborn
      Icon for Super User rankSuper User
      2 years ago

      Hi Anonymous 

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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!