I have 2 SharePoint List.
Project(Project_ID, Project_Title)
and
Project_Tasks(Project_ID, Tasks_Title) [here Project_ID is lookup column from Project list.]
I have data as:
I am able to generate the following report.
But My requirement is to generate a report like
In this case, the Pivoting will not help me. I have already tried.
Please help. Any reference link, tutorial video or suggestion is most welcome
Fun question!
You can use a Power Query feature named "Extract Values" to do this.
In my example, I renamed the Title column in each list to Project_ID and Tasks_Title. When these are brought into Power BI they are displayed with the original Title name and will need to be renamed.
Steps:
In your Project_Tasks list, update the Project_ID lookup to also display Project_Title.
In a new Power BI project, click Get Data and pick your SharePoint Project_Tasks lists. (Project list not needed.)
Click Edit, not Load. Your are now in Power Query.
Delete the uneeded columns. (a lot of them!) You should now see id (optional). Title (or Tasks_Title) and Project ID
If you renamed the Title column in SharePoint, rename the Title column here to Tasks_Title.
Click the Group By button in the Home or Transform ribbon.
Click the double headed arrow at the top of the Project_ID column, unselect all of the columns and select Project_Title and Title (or Project_ID). Uncheck "User original column name as prefix" and click OK.
If you renamed the Title column in SharePoint, rename the Title column here to Project_ID.
Click the Group By button in the Home or Transform ribbon.
Click Advanced.
Select Project_ID from the dropdown.
Click Add grouping and select Project_Title from the dropdown.
Click the Operation dropdown and select Add Rows.
Change the "New column name" to TaskInfo.
Click OK.
In the Add Column ribbon click Custom Column.
Enter this formula: =Table.Column([TaskInfo], "Tasks_Title") (This is the "magic" part 1.)
Change the column name to "Tasks".
Click OK.
Click the double headed arrow at the top of the TaskInfo column and click "Extract Values...". (This is the "magic" part 2.)
Select a delimiter from the dropdown list, or a custom value like ", ". Click OK.
You can now delete the TaskInfo column.
Click Close & Apply and continue with your report.
The result
My "M" code from the Power Query Advanced Editor (your URL and GUIDs will be different)
let
Source = SharePoint.Tables("https://yourDomain.sharepoint.com", [ApiVersion = 15]),
#"0019cad5-ca73-4195-808d-c94a82671472" = Source{[Id="0019cad5-ca73-4195-808d-c94a82671472"]}[Items],
#"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"}}),
#"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"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns2", {"Project_ID", "Project_Title"}, {{"TaskInfo", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Tasks", each Table.Column([TaskInfo], "Tasks_Title")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Tasks", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
#"Extracted Values"
I found a YouTube video that shows an example that is just a little more complex then your needs, but shows how it is done.
