Don't miss your chance to take the Fabric Data Engineer (DP-600) exam for FREE! Find out how by attending the DP-600 session on April 23rd (pacific time), live or on-demand.
Learn moreNext up in the FabCon + SQLCon recap series: The roadmap for Microsoft SQL and Maximizing Developer experiences in Fabric. All sessions are available on-demand after the live show. Register now
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 |
Solved! Go to 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.
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.
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.
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
| User | Count |
|---|---|
| 46 | |
| 43 | |
| 39 | |
| 19 | |
| 15 |
| User | Count |
|---|---|
| 68 | |
| 67 | |
| 31 | |
| 27 | |
| 24 |