Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jchapmanICT
New Member

Using PowerBI with Project for the Web - Reporting on Summary Tasks and associated SubTask progress

Hi All:

 

Hoping someone can help guide me as a relative noob to both Project for the Web and PowerBI.

 

I need to be able to report on progress on completion of subtasks for each Summary Task in my project.

 

In essence the project plan is setup as follows:

 

Site 1

SubTask 1

SubTask 2

Site 2

SubTask 1

SubTask 2

etc etc...

 

In the PowerBI template for Project for the Web, there doesn't appear to be any way to visualise this. Can anyone help please? Treat me as a complete noob! 🙂

 

Thank you

 

J

1 ACCEPTED SOLUTION
jchapmanICT
New Member

Hey Amit,

Thanks for the response. I have now realised that the tables and fields pulled in by the PowerBI template for Project on the Web are not complete. Pulling in the full dataset allows me to use a parent task field which effectively gives me a workaround for this. 

 

Thanks again

 

J

View solution in original post

16 REPLIES 16
MelanieEB
Regular Visitor

In cooperation with ChatGPT I was able to develop a funktion that uses the parent tasks to build up a hierarchy. In case anyone is interested in the code, pls. let me know. 

I would be interested. Please can you post it here and let us know how to use it?

Dear Nozama,

I hope the following is clear, if you have any questions, please let me know. 

Create a new Table (I called it Project Tasks Hierarchy) linked to Project Tasks Staging (Dataverse Table) with the following code 

let
Quelle = #"Project Tasks Staging",
AddHierarchyColumn = Table.AddColumn(Quelle, "Hierarchy", each GetHierarchy([msdyn_projecttaskid])),
#"Changed type" = Table.TransformColumnTypes(AddHierarchyColumn,{{"Hierarchy", type text}}),
AddParentTaskCount = Table.AddColumn(#"Geänderter Typ", "ParentTaskCount", each CountParentTasks([Hierarchie]))
in
AddParentTaskCount

To avaoid circular references, go back to Project Tasks Staging and create another linked Table called "Hierarchy Data" with this code. You need to make sure that the ParentTask ID column has no null values, therefore it adds a column called ParentTaskID that makes sure that values coming from msdyn_parenttask are no instead of null, otherwise the functions are not working:

let
Quelle = #"Project Tasks Staging",
#"AddColumn" = Table.AddColumn(Quelle, "ParentTaskID", each if([msdyn_parenttask])= null then "no" else [msdyn_parenttask])

in
#"AddColumn"

Right click on the Table "Project Tasks Hierarchy" and add a Function... called "GetHierarchyRecursive" with the following code:

let
GetHierarchyRecursive = (currentTaskID as text, hierarchyList as list) as text =>
let
currentTaskRecord = Table.SelectRows(HierarchyData, each [msdyn_projecttaskid] = currentTaskID){0},
parentTaskID = currentTaskRecord[ParentTaskID],
projectID = currentTaskRecord[msdyn_project],
hierarchyItem =
if parentTaskID <> "no" then
GetHierarchyRecursive(parentTaskID, hierarchyList & {currentTaskID})
else
projectID & "," & Text.Combine(List.Reverse(hierarchyList & {currentTaskID}), ",")
in
hierarchyItem
in
GetHierarchyRecursive

Right click on the table "Project Tasks Hierarchy" again and add another function called Get Hierarchy with the following code:

let
GetHierarchy = (currentTaskID as text) as text =>
let
hierarchie = GetHierarchyRecursive(currentTaskID, {})
in
hierarchie
in
GetHierarchy

In order to be able to highlight the different task levels in a Power BI Table visual, I added another function called CountParentTask. This counts how many parent tasks were found for this task and by using the numbers, you can use the conditional formatting of the table visual to improve readablity of the table and make clear which task belongs to which parenttask

let
GetParentTaskCount = (hierarchyString as text) as number =>
let
commaCount = List.Count(Text.Split(hierarchyString, ",")),
parentTaskCount = commaCount
in
parentTaskCount
in
GetParentTaskCount

As you are connected to Dataverse, the column names of the table Project Tasks Staging should be the same, but please check all the table and column names to make sure the codes are working for you. 

Sorry I forgot to change the word "Quelle" in the codes. It's the German word for "Source" so please change that accordingly. 

 

jchapmanICT
New Member

Hey Amit,

Thanks for the response. I have now realised that the tables and fields pulled in by the PowerBI template for Project on the Web are not complete. Pulling in the full dataset allows me to use a parent task field which effectively gives me a workaround for this. 

 

Thanks again

 

J

How do you mean "pull in all data" - please can you elaborate? I am having the same problem. Thanks.

Hi Nozama,

The default PowerBI template does not include all database tables. Changing this and adding in all tables (initially and while you're building your dashboards) allows you to access all of the fields used in Project for the Web.

 

Does that help? Happy to try to elaborate if you need me to.

 

J

If you could please elaborate that would be great! Currently the Project Dashboard takes input from PWA workspace (Project Online) and the respective Dataverse URL. So if there are tables missing - what additional source did you add to add those tables?!

Hi!

I admit I find the current mix of Microsoft Project versions confusing. We are using Microsoft Project for the Web, not Project Online and not Project Web Access - unless they've now changed the naming convention for these products. Really needs a Microsoft Licensing expert to answer any questions around the differences. 

 

What I do know is when I used the Powerbi Template, not ALL of the Project for the Web tables or fields were included. Looking at the PowerBI Data, you can choose additional tables and fields - so in this screenshot, any tables starting with "msdyn" are additional tables I added in. 

 

jchapmanICT_0-1695912970372.png

 

Not sure where you are in the world but if times match, I'd be happy to have a quick Teams call. I'd not call myself a complete expert though so it might end up feeling like the blind leading the blind... 🙂

 

J

Was able to do it, thanks! Haven't tried to see if that resolves the original issue of adding sub-tasks to tasks but will try!

I looked left, right and centre and honestly could not find any task which even had the word "parent" in it - unless there is a clever way to play with the IDs etc. 🤷‍♂️

Hey Nozama,

 

Sorry to hear you're still having problems. It took me a while and I am really no expert so I would hate to describe to you a method which is not as Microsoft intend it to be......

 

My advice to you is to log a ticket with Microsoft. You can do this via your tenancy - https://admin.microsoft.com/AdminPortal/#/support

 

Alternatively I'd be happy to have a quick Teams call to see if I can talk you through this...

 

What I will say is that there is a field entitled "msdyn_parenttask" which you can use as a filter - i.e. "Only show me records where "msdyn_parenttask" is not blank" - then use the "Project Task Name" field to display the Task name etc etc. 

 

If that doesn't work - a Teams call, or log a ticket with Microsoft - they were pretty responsive to my queries.

 

Best wishes (and good luck)

 

John

 

Hi John, I've been struggling with this for the last days and I would be so glad to know how you managed to use the partent task (which is in my dataset) to visualize the tasks acc. to their hirarchy. I tried to make a hirarchy direktly in the data model, but the problem is that sub-subtasks are not allocated correctly.  I.e. a task is only allocated to its direct partent, not its parent parent. 
Thanks a lot for any hint. Regards Melanie

Hi Melanie:

 

I wish I could help you. I'm really no expert. I have not actually touched this since April. My recommendation is to log a ticket with Microsoft - they were super helpful with me and pretty responsive. 

 

J

Hi John,

thanks for taking the time to reply. I will check with Microsoft directly.

Seasons Greeting!

Melanie

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.