Forum Discussion

shaebert's avatar
shaebert
Icon for Helper III rankHelper III
3 years ago
Solved

Need Help Grouping and Combining Rows

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

 

Before:

 

Campaign    ContactURL
1Joe Smithgoogle.com
1Joe Smithyahoo.com
1Jerry Wilsongoogle.com
2Jerry Wilsongoogle.com
2Jerry Wilsonyahoo.com
2Jerry Wilson      msn.com

 

After

 

Campaign  NameURL
1Joe Smithgoogle.com, yahoo.com
1Jerry Wilson  google.com
2Jerry Wilson  google.com, yahoo.com, msn.com  

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.