Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
| |||
P-2 | Project-2 |
|
In this case, the Pivoting will not help me. I have alreadty tried.
Please help. Any reference link, tutorial video or suggestion is most welcome
You can download my sample file here.
Solved! Go to Solution.
Hi @aakashdmorya
Please see the link to the pbix file below.
https://drive.google.com/file/d/1nyNdko2K-q5O1zan-P02b5jCKR9HMo1w/view?usp=sharing
Let me know if you expirience any problems
Mariusz
Hi @aakashdmorya
You can use Query Editor, please see the below M code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCtA1VNJRCijKz0pNLgGzQxKLs4uBrFgd3LJGUFkjJFkjuKwxXlkTvLKmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project_ID = _t, Project_Title = _t, Tasks = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project_ID", type text}, {"Project_Title", type text}, {"Tasks", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Project_ID", "Project_Title"}, {{"Tasks", each _[Tasks], type list}}), #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Tasks", each Text.Combine(List.Transform(_, Text.From), "; "), type text}) in #"Extracted Values"
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mariusz Thanks allot for your reply. But I have 2 datasource. This query will be applicable to which data source? Project or Tasks? Reply will be appriciated.
Hi @aakashdmorya
As you can see I have applied it to the merdged tables that you have listed third, however you copuld applie this to the Tasks table if you wich.
Hope this helps
Mariusz
Hi @Mariusz ,
I tried but it does not work at my end.
I have updated my question to include the PowerBI file also.
If you have any reference example or link you can share those.
Thank you
Hi @aakashdmorya
Please see the link to the pbix file below.
https://drive.google.com/file/d/1nyNdko2K-q5O1zan-P02b5jCKR9HMo1w/view?usp=sharing
Let me know if you expirience any problems
Mariusz
Hi @Mariusz
Thanks for your time and answer. That worked like charm and I am able to generate it to my report as well from SharePoint source. But I have one more additional comment.
Like you have combined the tasks together into one column but that all are actually a string concate. But what if I would like 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. Again, any reference link will be helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |