Need Help Grouping and Combining Rows

[shaebert]

Hello, I have a table of data that I need help transforming. Below is a before/after example. 

Before:

Campaign	Contact	URL
1	Joe Smith	google.com
1	Joe Smith	yahoo.com
1	Jerry Wilson	google.com
2	Jerry Wilson	google.com
2	Jerry Wilson	yahoo.com
2	Jerry Wilson	msn.com

After

Campaign	Name	URL
1	Joe Smith	google.com, yahoo.com
1	Jerry Wilson	google.com
2	Jerry Wilson	google.com, yahoo.com, msn.com

[Anonymous · Accepted solution]

Hi @shaebert ,

Please try below steps:

1. below is my test table

Table:

2. Please try to create a new table with below dax formula:

Table2 =
VAR tmp =
    SUMMARIZE ( 'Table', [Campaign], [Contact] )
RETURN
    ADDCOLUMNS ( tmp, "URL", CALCULATE ( CONCATENATEX ( 'Table', [URL] & "," ) ) )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Campaign", type text}}, "en-IN"),{"Campaign", "Contact"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"URL", "Index"}, {"URL", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Partition", {{"Index", each "URL " & Text.From(_, "en-IN"), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Prefix", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Campaign", "Contact"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Index]), "Index", "URL")
in
    #"Pivoted Column"

Hope this helps.