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

[jchapmanICT]

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

[MelanieEB]

After struggling with this for months, I finally found a very easy solution to sort the Project Tasks in the Power Query table for Power BI according the hierarchy they show in the original projects for the web project plan. 

The msdyn_projecttask table from Dataverse does have a column called msdyn_displaysequence. You need to add a column first, that fills the msdyn_displaysequence values with leading zeros so that they all have the same format. Then add a new column that combines the texts from msdyn_project (which is the project id) and the formattet msdyn_displaysequence column. 

When you sort this column from A-Z all tasks will be shown in the same order as they appear in the project plan. 

Code:

let
Source = msdyn_projecttask,
AddDisplaySequenceAsText = Table.AddColumn(Source, "DisplaySequenceText", each Text.From([msdyn_displaysequence])),
ModifyDisplaySecquence = Table.AddColumn(AddDisplaySequenceAsText, "DisplaySequence", each
let

parts = Text.Split([DisplaySequenceText], ","),

beforeComma = List.First(parts),
afterComma = if List.Count(parts) > 1 then List.Last(parts) else "",

formattedBeforeComma = Text.PadStart(beforeComma, 5, "0"),

formattedValue = formattedBeforeComma & "," & afterComma
in
formattedValue
),
AddHierarchy = Table.AddColumn(ModifyDisplaySecquence, "Hierarchy", each Text.Combine({[msdyn_project], Text.From([DisplaySequence])}, ","))

in
AddHierarchy

[MelanieEB]

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. 

[Nozama]

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

[MelanieEB]

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. 

[MelanieEB]

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

[jchapmanICT · Accepted solution]

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

[Nozama]

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

[jchapmanICT]

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

[Nozama]

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?!

[jchapmanICT]

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. 

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

[lizmplsms]

I'm trying to add that projectlabel in to my data. How do you do that? 

[Nozama]

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!

[Nozama]

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. 🤷‍♂️

[jchapmanICT]

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

[MelanieEB]

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

[jchapmanICT]

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

[MelanieEB]

Hi John,

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

Seasons Greeting!

Melanie

[amitchandak]

@jchapmanICT , Can you share more details, have you tried Matrix Visual ?

or

Gantt Chart, project time line visual
https://appsource.microsoft.com/en/product/power-bi-visuals/WA104380765?tab=Overview
https://www.mpug.com/articles/how-to-create-an-amazing-gantt-chart-in-power-bi/
https://blog.pragmaticworks.com/power-bi-custom-visuals-as-timeline
https://www.youtube.com/watch?v=SO4mk1H94OA
https://xviz.com/visuals/gantt-chart/