Combine rows with duplicate identifiers while adding additional columns

Question

Before:&nbsp;NameSpeciesActivityStart DateEnd Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PusheenFelineSnacking10/1/201910/10/2019PusheenFelineNapping10/2/201910/12/2019PuppyDoggiePlaying10/3/201910/13/2019&nbsp;After:&nbsp;NameSpeciesActivity 1Start Date 1End Date 1Activity 2Start Date 2End Date 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PusheenFelineSnacking10/1/201910/10/2019Napping10/2/201910/12/2019PuppyDoggiePlaying10/3/201910/13/2019NANANA&nbsp;How do I do this in Power BI Desktop? Thanks!&nbsp;&nbsp;&nbsp;

dax · Accepted Answer

Hi daisyhu,&nbsp;
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"
&nbsp;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.
&nbsp;

Forum Discussion

Combine rows with duplicate identifiers while adding additional columns

1 Reply

Related content

Identifying duplicates

Identifying Duplicates

Identify duplicates

Identifying duplicates

Identify duplicates

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

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