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

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

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.