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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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



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



View solution in original post

Frequent Visitor

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. 


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

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
AddHierarchy = Table.AddColumn(ModifyDisplaySecquence, "Hierarchy", each Text.Combine({[msdyn_project], Text.From([DisplaySequence])}, ","))


Frequent 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 

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]))

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:

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


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

GetHierarchyRecursive = (currentTaskID as text, hierarchyList as list) as text =>
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})
projectID & "," & Text.Combine(List.Reverse(hierarchyList & {currentTaskID}), ",")

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

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

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

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

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. 


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



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.



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


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... 🙂



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

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 -


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)




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. 



Hi John,

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

Seasons Greeting!


Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.