Converting rows to column grid in SharePoint Online List Report

[aakashdmorya]

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:

Project:

Project_ID	Project_Title
P-1	Project-1
P-2	Project-2
P-3	Project-3

Project_Tasks:

Project_ID	Tasks_Title
P-1	Tasks-1
P-1	Tasks-2
P-2	Tasks-3
P-2	Tasks-4
P-2	Tasks-5

I am able to generate the following report.

Project_ID	Project_Title	Tasks
P-1	Project-1	Tasks-1
P-1	Project-1	Tasks-2
P-2	Project-2	Tasks-3
P-2	Project-2	Tasks-4
P-2	Project-2	Tasks-5

But My requirement is to generate a report like

Project_ID	Project_Title	Tasks
P-1	Project-1	Tasks-1 Tasks-2
P-2	Project-2	Tasks-3 Tasks-4 Tasks-

In this case, the Pivoting will not help me. I have alreadty tried.

You can download my sample file here.

PowerBI Report File

With the help of this answer I was able to merge the data to the single column, but my requirement was to add every task as a new column under "Tasks" header? Find reference below

Because I have to put the background color on each of the tasks based on its status.

Any reference link will be helpful.

[stvsurf]

I would use an index column to get be able to pivot the tasks in Power BI, then you can use a conditional formatting on a status field in Power BI.

This is what I would do on the projects table in Power Query:

Apologies for posting M as an image, but I got an HTML error on the code