Delimiting Data Pairs into Different Rows

[Anonymous]

Hello everyone

I have a situation where I want to delimit the following data into different rows:

From:

Index,Data

1,A-B-C-D

2,A-B

To:

Index,Data

1,A-B

1,B-C

1,C-D

2,A-B

I tried following the advice in this post but am unable to adapt it to my case, since there is data that is repeated in each row.

Thank you!

[ChrisMendoza · Accepted solution]

@Anonymous -

This should get it done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUddJ11nVRitWJVjKC8JViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Data", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}, {"Data.4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Count", each _, type table [Index=number, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([Count],"Index2",1,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let tblName = [AddIndex] in Table.AddColumn([AddIndex],"nextVal", each tblName{[Index2]}[Value])),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Index", "Attribute", "Value", "Index2", "nextVal"}, {"Custom.1.Index", "Custom.1.Attribute", "Custom.1.Value", "Custom.1.Index2", "Custom.1.nextVal"}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom.1", {{"Custom.1.nextVal", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Custom.1.nextVal] <> null)),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Custom.1.Value", "Custom.1.nextVal"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Index", "Merged"})
    
in
    #"Removed Other Columns"