Power BI - Convert a Column into a Comma Delimited list.

When grouping data have you ever wanted to keep a summary of the details? Maybe as a comma delimited list as a single column?

I've put together a sample project here that takes projects and their tasks and groups them by project and summarizes each project by summing project hours and by creating a comma delimited list of tasks.

Here's the source data. It happens to be from a SharePoint list with lookup columns for Project ID and Title. It could be from any other source such as a two table join of Projects and Tasks.


   

Here's the result I wanted:

 

If you have worked with Power BI much at all, getting the subtotals by project is probably not too much of a challenge. The comma delimited list of task name might be a bit harder.

Here's the steps, slightly generalized:

1. In Power BI use Get Data to get the one table like my starting example, or two tables that you then join to get a result that looks like my example.
1. Make sure you click Edit and not Load as the steps that follow occur in the Power Query Editor.
2. For a SharePoint source remove all of the unneeded columns. Here's what I removed: "FileSystemObjectType", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ComplianceAssetId", "Project_IDId", "ID.1", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Author", "Editor"
3. Rename any columns that did not come in the way you like.
4. Here's my data at this point. As my SharePoint list used lookup columns, all of the looked up data is stored here as a "Record". (If your list does not include a "Record" column, you can skip steps 5 and 6.)
   
5. To extract data from the Record column, click the double headed arrow, unselect all of the columns and select just the columns you want to keep. I selected Title and Project_Title. (Even if you renamed the "Title" column in your SharePoint list, it will still be imported here as "Title".)
   
6. Rename the "Title" column to "Project_Title". (or any name of your choice!)
   The result should now look like this.
   
7. If you also brought in a Task ID or Task date column, you may want to sort on it now.
8. Click Group By in the ribbon.
1. Group by the project columns, "Project_ID", and "Project_Title" in my example.
2. Click Advanced so you can create two columns during the grouping. 
3. The first trick needed to get the task names out to a comma delimited list is to use the "All Rows" operation. I named this new column "TaskInfo", but any name will work as we will delete this column later.
4. Click Add aggregation and add a Sum operation on the Hours column. I named this column "Total Hours".
    
5. Click OK. Note that the TaskInfo is a column of Tables. Each table contains a list of the tasks.
   
9. In the Add Column ribbon click Custom Column.
1. Name the column "Tasks".
2. Enter this formula: =Table.Column([TaskInfo], "Tasks_Title")  (This is the second part of the trick...)  This extracts the Task Title data from the table created by the group and then stores it as a new List column.
   
3. Click OK.  Note that the new column is a column of Lists.
   
10. The "TaskInfo" column is no longer needed and can be deleted now.
11. Now we need to extract the List data for each project into a comma delimited list.
1. At the to top of the "Tasks" List column click the double headed arrow and click Extract Values...
   
2. Select your delimiter from the drop down or create a custom delimiter. I selected Comma.
   
3. Click OK. 
12. All done! Click Close & Apply and create your visuals!

  


Here's the M code (Advanced Editor) that was generated by this project. Note that I did not follow any best practices like naming each step! But I did add some comments.

let
    // Get the SharePoint data
    Source = SharePoint.Tables("https://microsmith.sharepoint.com", [ApiVersion = 15]),

    #"0019cad5-ca73-4195-808d-c94a82671472" = Source{[Id="0019cad5-ca73-4195-808d-c94a82671472"]}[Items],

    // Clean up the SharePoint data
    #"Renamed Columns" = Table.RenameColumns(#"0019cad5-ca73-4195-808d-c94a82671472",{{"ID", "ID.1"}}),


    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"FileSystemObjectType", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId", "ComplianceAssetId", "Project_IDId", "ID.1", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Author", "Editor"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Title", "Tasks_Title"}, {"q2qp", "Hours"}}),

    #"Expanded Project_ID" = Table.ExpandRecordColumn(#"Renamed Columns1", "Project_ID", {"Title", "Project_Title"}, {"Title", "Project_Title"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Project_ID",{{"Title", "Project_ID"}}),

    // group the data
    #"Grouped Rows" = Table.Group(#"Renamed Columns2", {"Project_ID", "Project_Title"}, {{"TaskInfo", each _, type table [Project_ID=text, Tasks_Title=text, Project_Title=text, Hours=number]}, {"Total Hours", each List.Sum([Hours]), type number}}),

    // step 1 to get the comma delimited list
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Tasks", each Table.Column([TaskInfo], "Tasks_Title")),

    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"TaskInfo"}),

    // step 2 to get the comma delimited list
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Tasks", each Text.Combine(List.Transform(_, Text.From), ","), type text})


in
    #"Extracted Values"