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.
