Combine rows with duplicate identifiers while adding additional columns

Hi daisyhu, 

You could try to use below M code to see whether it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVXSUQouSE3OTC0GshyTSzLLMksqQYIliUUlCi6JJSAVrnkpEGasTrRSQGlxRmpqHlDYLTUnMw9sQl5icnZmXjqQaWigb6hvZGBoCWUbQDg4NPolFhTA9Rkh6zNC1ldQAHKSS356eiZIV0BOYiVclzGyLignNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Activity", "Start Date", "End Date"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Index", 0, 1),
    #"Grouped Rows1" = Table.Group(#"Added Index", {"Name", "Species"}, {{"ALL", each _, type table [Name=text, Species=text, Merged=text, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([ALL], "GID", 1,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ALL"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Merged", "GID"}, {"Custom.Merged", "Custom.GID"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.GID", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.GID", type text}}, "en-US")[Custom.GID]), "Custom.GID", "Custom.Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"1.1", "1.2", "1.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"2.1", "2.2", "2.3"})
in
    #"Split Column by Delimiter1"

 Then rename column name to you want.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.