Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
Have a requirement to extract the "Tasks Summary Names" from a column called Task Name based on the TaskWBS column's value.
Our data is extracted from MS Project Online, using the api call in this format => https://<tenantname>.sharepoint.com/sites/pwa/_api/Projectdata/Tasks
Now, all Tasks that have subtasks are called as Task Summary Names. That is understood by the WBS value.
So, if WBS value is "blank", then that is the top most task, "Task 1", under which all tasks are present. If the value of a task, "Task 2", is "1", then the task summary of "Task 2" is "Task 1". Similarly, if there is a subtask under "Task 2" as "T 3" with the WBS value of 1.1, then summary name of "T 3" is "Task 2" and so on.
Something like this: -
Now, in Power BI, extracting the Tasks table means that it is extracting all TaskNames as simple tasks and only way to differentiate is to use the TaskWBS table.
We have data in the below format: -
ProjectName | TaskName | TaskWBS | Text Before Delimiter |
AS01 | AS01 | ||
AS01 | memo PHF writing data TTT mtg | 3 | |
AS01 | memo PHF review | 4 | |
AS01 | T18m DLPG data treatment | 2.2 | 2 |
AS01 | global project management (TDT, mtg,..) | 1 | |
AS01 | T9m | 2.4 | 2 |
AS01 | T12m | 2.5 | 2 |
AS01 | T18m | 2.6 | 2 |
AS01 | T24m | 2.7 | 2 |
AS01 | T24m DLPG | 2.3 | 2 |
AS01 | T20m | 2.8 | 2 |
AS01 | T24m | 2.9 | 2 |
AS01 | T24m DOPG | 2.1 | 2 |
AS01 | Stability plan 2-8°C | 2 | |
AS01 | T18m DLPG lab | 2.1 | 2 |
HRD - i88 | HRD - i88 | ||
HRD - i88 | Project Management | 1 | |
HRD - i88 | Project Milestones | 1.1 | 1 |
HRD - i88 | Start of Project | 1.1.1 | 1.1 |
HRD - i88 | Implementation of Project | 1.1.2 | 1.1 |
HRD - i88 | work shop prod | 1.1.3 | 1.1 |
HRD - i88 | Phase 0 document | 1.1.4 | 1.1 |
HRD - i88 | Revue document | 1.1.5 | 1.1 |
HRD - i88 | Effectivi | 1.1.7 | 1.1 |
HRD - i88 | Formation | 1.1.6 | 1.1 |
Now, we have Project Name, Task Name and TaskWBS from the source. As seen, the Task WBS value repeats itself as the Project changes since it only denotes task level.
The Text Before Delimiter is a custom column, I created to extract the WBS values that we need to get the Summary Names of its corresponding Task. Formula - Table.AddColumn(#"Filtered Rows", "Text Before Delimiter", each Text.BeforeDelimiter([TaskWBS], ".", {0, RelativePosition.FromEnd}), type text)
Now, currently using a column called "TaskIsSummary", I am able to filter out all Task Names that are summaries. In a separate table. Like this: -
So, currently I want to use this filtered table to get my data in the following format: -
The Task Summary Name is the expected column output.
As in the above pic, the Task Names are repeating now besides the subtasks to denote its parent task.
I am able to achieve this using PQ, by merging the new table with the existing table (Linking Project IDs of both and TaskWBS from new table with Text before Delimiter column in the original table, so basically 4 columns linked together). But I am aware that merging has performance costs while refreshing.
Hence, if this output is possible as a DAX measure, would be better for performance I guess.
Please Help
Thanks!
Solved! Go to Solution.
Update - I was not able to find any solution as a DAX measure.
Therefore, I stuck with my Power Query solution only.
But instead of doing this merging of Linking Project IDs of both tables and TaskWBS from new table with Text before Delimiter column in the original table in the Dataset itself, I moved this transformation to a Dataflow.
This improved the performance significantly. In the Dataflow, this merging takes hardly 10 seconds.
Update - I was not able to find any solution as a DAX measure.
Therefore, I stuck with my Power Query solution only.
But instead of doing this merging of Linking Project IDs of both tables and TaskWBS from new table with Text before Delimiter column in the original table in the Dataset itself, I moved this transformation to a Dataflow.
This improved the performance significantly. In the Dataflow, this merging takes hardly 10 seconds.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
82 | |
62 | |
61 | |
58 |
User | Count |
---|---|
159 | |
114 | |
100 | |
75 | |
65 |