Combine rows with duplicate identifiers while adding additional columns

[Anonymous]

Before:

Name	Species	Activity	Start Date	End Date
Pusheen	Feline	Snacking	10/1/2019	10/10/2019
Pusheen	Feline	Napping	10/2/2019	10/12/2019
Puppy	Doggie	Playing	10/3/2019	10/13/2019

After:

Name	Species	Activity 1	Start Date 1	End Date 1	Activity 2	Start Date 2	End Date 2
Pusheen	Feline	Snacking	10/1/2019	10/10/2019	Napping	10/2/2019	10/12/2019
Puppy	Doggie	Playing	10/3/2019	10/13/2019	NA	NA	NA

How do I do this in Power BI Desktop? Thanks!

[dax · Accepted solution]

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.