This article explains how to visualize tasks from Microsoft Planner in Power BI in a process flow format (we use the Task Stream PRO – Process Flow Chart Power BI visual by Softcial to do so).

Task Stream PRO intuitively displays task flows, milestones and dependencies, perfect for reporting and team overviews. It shows how to visualize tasks using the Microsoft Planner hierarchy: 

This guide covers importing, connecting, transforming data, linking fields, and customizing the visual. To illustrate these steps, the following screenshot presents an example Power BI report built with the Task Stream PRO visual, using construction project data to demonstrate how tasks can be organized and visually tracked. 

Before you start, make sure you have: 

1. Planner data access via Dataverse. 

2. Power BI Desktop. 

3. The Task Stream visual developed by Softcial Visuals: 

• Task Stream PRO – Process Flow Chart: Full access with no limits, featuring drilldown navigation, dynamic task sizing and complete design flexibility.  

• Task Stream – Process Flow Chart (Free Version): A fully functional version. You can personalize colors, fonts and sizes for tasks and milestones to match your branding or reporting needs. 

Planner Data Structure for Task Stream PRO 

The Task Stream PRO – Process Flow Chart visual requires data to be structured into three main tables: “Milestones”, “Milestones Tasks” and “Status”. To map our Plan > Bucket > Task > Checklist hierarchy, we will do the following: 

• Milestones: This will represent your Planner “Plans”. 

• Milestones Tasks: This table is key and will consolidate data from different Dataverse tables to represent multiple levels: 

1. Planner “Buckets” (from msdyn_projectbucket) will be treated as Level 0 tasks. 
2. Planner “Tasks” (from msdyn_projecttask) within those buckets will be treated as Level 1 tasks. 
3. Planner “Checklists” (Checklist items, from msdyn_projectchecklist) within Planner tasks will be treated as Level 2 tasks. 

• Status: This will represent the statuses of tasks/checklist items, allowing for color customization. 

1. Connect to Planner Data via Dataverse 

If you haven’t done this part yet, refer to the article: Consuming Microsoft Planner Data in Power BI Using Dataverse, but in short: 

1. Open Power BI Desktop 

2. Go to Home > Get Data > Dataverse 

3. Sign in to your Microsoft 365 environment 

4. Select the following tables: msdyn_project, msdyn_projectbucket, msdyn_projecttask, msdyn_projectchecklist  

2. Data Transformation and Preparation 

2.1. Prepare the “Milestones” Table (Planner Plans) 

This table will represent your Planner plans. To transform your msdyn_project table into the “Milestones” table, follow these steps in Power Query: 

1. In the Power Query “Queries” pane, select the msdyn_project table. 

Choose Columns: 

• Go to the Home tab. 

• Click on Choose Columns. 

• In the dialog window, select only the following columns: msdyn_projectid, createdon, msdyn_subject, and msdyn_progress. Then click OK. 

Rename Columns: 

Double-click the header of each column or select them and use the “Rename” button on the “Transform” tab to rename them: 

• msdyn_projectid → Id 

• msdyn_subject → Name 

• msdyn_progress → Completed 

Sort Rows: 

• Click the small arrow next to the createdon column header. 

• Select Sort Ascending. This step is crucial for establishing a custom order for your plans. Although we are sorting them here by creation date for chronological tracking, you can adapt the sorting according to your specific needs (e.g., by priority, alphabetical name, etc.) by modifying the sorting column. 

Add Index Column: 

• Go to the Add Column tab. 

• Click on Index Column and select From 0. (This will create a new column named “Index”). 

Rename the Index Column: 

• Double-click the “Index” column header and rename it to Order. 

Rename this query to “Milestones” 

With these steps, your msdyn_project table will be transformed and ready to function as the “Milestones” table in the visual. 

2.2. Prepare the “Milestones Tasks” Table (Planner Buckets, Tasks, and Checklists) 

This table will be the most complex, as it will consolidate Planner buckets, tasks, and checklist items, defining their hierarchy using Level and Parent. 

2.2.1. Transform msdyn_projectchecklist (Level 2 – Checklists): This query will focus on checklist items, which will be nested under their respective parent tasks. 

• In the “Queries” pane, select the dbo_msdyn_projectchecklist table. 

Choose Columns: 

• Go to the Home tab. 

• Click on Choose Columns. 

• In the dialog window, select only the following columns: msdyn_projectchecklistid, createdon, modifiedon, msdyn_name, msdyn_projectchecklistcompleted, msdyn_projecttaskid, and the “Record” type column msdyn_projecttask. Click OK. 

Expand Column msdyn_projecttask: 

• Click the expand icon [»] in the header of the msdyn_projecttask column. 

• In the pop-up window, select msdyn_project. Click OK. 

Add Conditional Column Status: 

• Go to the Add Column tab. 

• Click on Conditional Column. 

• New column name: Status. 

• Configure the clause: 

• If msdyn_projectchecklistcompleted equals false Then 0 

• Else 1 

• Click OK. 

Add Custom Column Level: 

• Go to the Add Column tab. 

• Click on Custom Column. 

• Name the new column Level and enter 2 into the formula. Click OK. 

Rename Columns: 

• Double-click the header of each column or select them and use the “Rename” button on the “Transform” tab to rename them: 

• msdyn_projectchecklistid → Id 

• msdyn_name → Name 

• msdyn_projecttask.msdyn_project → Milestone Id 

• createdon → Start Date 

• modifiedon → Finish Date 

• msdyn_projecttaskid → Parent 

Sort Rows to Generate the Order Field: 

• Click the small arrow next to the Start Date column header. 

• Select Sort Ascending 

Group Rows to add Order within each group (Milestone Id): 

• Go to the Transform tab. 

• Click on Group By. 

• In “Group by”, select Milestone Id. 

• Add aggregation: 

• New column name: GroupedData. 

• Operation: All Rows. 

• Click OK. 

Add Custom Column for Order: 

• Go to the Add Column tab. 

• Click on Custom Column. 

• New column name: OrderColumn. 

• Formula: Table.AddIndexColumn([GroupedData], “Order”, 1, 1, Int64.Type) 

• Click OK. 

Expand OrderColumn: 

• Click the expand icon [»] in the header of the OrderColumn column. 

• Uncheck Use original column name as prefix. Click OK. 

Remove Unnecessary Columns: 

• Select the columns: GroupedData, msdyn_projectchecklistcompleted and Milestone Id.1. 

• Go to the Home tab. 

• Click on Remove Columns and select Remove Columns. 

Reorder Columns: 

• To maintain consistent order in the final table, you can drag columns to appear in the following order: Id, Name, Milestone Id, Status, Start Date, Finish Date, Order, Level, Parent. 

Rename this query to “Checklists_Transformed”. 

2.2.3. Transform msdyn_projecttask (Level 1 – Tasks): This query will represent your individual “Tasks”, which will be nested under buckets. 

• In the “Queries” pane, select the msdyn_projecttask table. 

Choose Columns: 

• Go to the Home tab. 

• Click on Choose Columns. 

• In the dialog window, select only the following columns: msdyn_projecttaskid, createdon, msdyn_subject, msdyn_displaysequence, msdyn_effort, msdyn_finish, msdyn_progress, msdyn_project, and msdyn_projectbucket. Click OK. 

Add Conditional Column Status: 

• Go to the Add Column tab. 

• Click on Conditional Column. 

• New column name: Status. 

• Configure the clauses: 

• If msdyn_progress equals 0 Then 0 

• Else if msdyn_progress equals 1 Then 1 

• Else 2 

• Click OK. 

Rename Columns: 

• Double-click the header of each column or select them and use the “Rename” button on the “Transform” tab to rename them: 

• msdyn_projecttaskid → Id 

• msdyn_subject → Name 

• msdyn_project → Milestone Id 

• createdon → Start Date 

• msdyn_finish → Finish Date 

• msdyn_projectbucket → Parent 

• msdyn_effort → Effort 

• msdyn_displaysequence → Order 

Remove the msdyn_progress column: 

• Select the msdyn_progress column. 

• Go to the Home tab. 

• Click on Remove Columns and select Remove Columns. 

Add Custom Column Level: 

• Go to the Add Column tab. 

• Click on Custom Column. 

• Name the new column Level and enter 1 in the formula. Click OK. 

Reorder Columns: 

• To maintain consistent order, you can drag columns to appear in the following order: Id, Name, Milestone Id, Status, Start Date, Finish Date, Order, Level, Parent, Effort. 

• Rename this query to “Tasks_Transformed”. 

2.2.1. Prepare the Auxiliary Table “Tasks_BucketSum” This query will calculate aggregated values (maximum finish date, total effort and a consolidated status) for each bucket based on the tasks it contains. For this example, we have defined the following aggregation logics for each bucket, but it is important to note that these can be fully adapted according to the specific needs of your report: 

• Finish Date (MaxFinishDate): The maximum finish date of all tasks within each bucket has been chosen. This is useful for seeing when the last work in a bucket is expected to be completed. 

• Effort (TotalEffort): Calculated as the total sum of effort for all tasks contained in the bucket. This provides an aggregated view of pending or completed work at the bucket level. 

• Status (BucketCalculatedStatus): The defined logic is as follows: 

1. If ALL task statuses (msdyn_progress) within the bucket are equal to 1, the bucket status will be 1 (Completed). 
2. If ALL task statuses (msdyn_progress) within the bucket are equal to 0, the bucket status will be 0 (Not Started). 
3. Otherwise, the bucket status will be 2 (In Progress).  

• In the “Queries” pane of the Power Query Editor, locate the Tasks_Transformed table. 

• Right-click on Tasks_Transformed and select Duplicate. 

• Right-click on the new duplicated query (usually named “ Tasks_Transformed (2)”) and select Rename. Name it Tasks_BucketSum. 

Group Rows (Group By): 

• Go to the Transform tab. 

• Click on Group By. 

• Select the Advanced option. 

• In the “Group by” section, select Parent. 

• Add aggregation 1: 

1. New column name: MaxFinishDate 
2. Operation: Max 
3. Column: Finish Date 

• Add aggregation 2: 

1. New column name: TotalEffort 
2. Operation: Sum 
3. Column: Effort 

• Add aggregation 3: 

1. New column name: AllTasksForBucket 
2. Operation: All Rows 
3. Click OK. 

Add Custom Column BucketCalculatedStatus: 

• Go to the Add Column tab. 

• Click on Custom Column. 

• Name the new column BucketCalculatedStatus. 

• Enter the following formula in the “Custom column formula” box: 

if List.AllTrue(List.Transform([AllTasksForBucket][Status], each _ = 1)) then 1 

else if List.AllTrue(List.Transform([AllTasksForBucket][Status], each _ = 0)) then 0 

else 2 

• Click OK. 

Remove the AllTasksForBucket column: 

• Right-click on the AllTasksForBucket column header and select Remove. 

2.2.2. Transform msdyn_projectbucket (Level 0 – Buckets) This query will represent your “Buckets” as the top level of the task hierarchy. 

• In the “Queries” pane of the Power Query Editor, select the msdyn_projectbucket table. 

Choose Columns: 

• Go to the Home tab. 

• Click on Choose Columns. 

• In the dialog window, select only the following columns: msdyn_projectbucketid, msdyn_name, msdyn_project, createdon, and msdyn_displayorder. Click OK. 

Add Custom Column Level: 

• Go to the Add Column tab. 

• Click on Custom Column. 

• Name the new column Level and enter 0 in the formula. Click OK. 

Rename Columns: 

Double-click the header of each column or select them and use the “Rename” button on the “Transform” tab to rename them: 

• msdyn_projectbucketid → Id 

• msdyn_name → Name 

• msdyn_project → Milestone Id 

• createdon → Start Date 

• msdyn_displayorder → Order 

Merge Queries: 

• Go to the Home tab. 

• Click on Merge Queries (and select “Merge Queries” to apply changes to the current table). 

• In the “Merge” window, ensure that the current table (msdyn_projectbucket or as you renamed it) is selected as the “Primary table”. 

• In the “Secondary table for merging” dropdown menu, select your Tasks_BucketSum query. 

• Select the Id column in the primary table (msdyn_projectbucket) and the Parent column in the secondary table (Tasks_BucketSum). 

• In “Join Kind”, choose Left Outer (all from first, matching from second). Click OK. 

Expand Columns from the Merged Table: 

• Click the expand icon [»] in the header of the new Tasks_BucketSum column. 

• In the pop-up window, uncheck (Select All Columns). 

• Check the boxes next to MaxFinishDate, TotalEffort, and BucketCalculatedStatus. 

• Uncheck Use original column name as prefix. Click OK. 

Rename Expanded Columns: 

• Double-click the header of the MaxFinishDate column and rename it to Finish Date. 

• Double-click the header of the TotalEffort column and rename it to Effort. 

• Double-click the header of the BucketCalculatedStatus column and rename it to Status. 

Reorder Columns: 

• To maintain consistent order, you can drag columns to appear in the following order: Id, Name, Milestone Id, Status, Start Date, Finish Date, Order, Level, Effort. 

Rename this query to “Buckets_Transformed”. 

2. Combine (Append) Tables in Power Query for “Milestones Tasks”: This is the final stage where all transformed level tables are joined to create the complete hierarchy. 

• In the “Queries” pane of the Power Query Editor, go to the Home tab. 

• Click on Append Queries and select Append Queries as New. 

• In the “Append” window, select Three or more tables. 

• Add the following transformed queries to the list of tables to append: 

1. Buckets_Transformed 
2. Tasks_Transformed 
3. Checklists_Transformed 

• Click OK. 

Replace Null Values in Status: 

• Select the Status column. 

• Go to the Transform tab. 

• Click on Replace Values. 

• In the “Value To Find” box, type null. 

• In the “Replace With” box, type 0. 

• Click OK. 

Replace Null Values in Effort: 

• Select the Effort column. 

• Go to the Transform tab. 

• Click on Replace Values. 

• In the “Value To Find” box, type null. 

• In the “Replace With” box, type 0. 

• Click OK. 

Rename the final combined table to “Milestones Tasks”. 

3. Once you have completed all these transformations and the “Milestones Tasks” table looks correct in the Power Query Editor, click “Close & Apply” on the Home tab to load the table into your Power BI model. 

1.3. Create the “Status” Table 

This table is fundamental for interpreting the different states of tasks and buckets in your visual, assigning a descriptive name and color to each status ID (0, 1, 2). 

For this example, the Status table has been created manually in Power BI using DAX code. This method is useful for small, static tables with predefined data. 

Manual creation of the “Status” table using DAX: 

1. In Power BI Desktop, go to the Modeling tab in the ribbon. 

2. Click on New table. 

3. In the formula bar that appears, paste the following DAX code: 

Status = DATATABLE ( “Id”, INTEGER, “Name”, STRING, “Color”, STRING, { { 0, “Not Started”, “#EFEFEF” }, { 1, “Completed”, “#C6D79E” }, { 2, “In Progress”, “#BBCDE8”} } ) 

Once created, you will see the “Status” table in the “Data” pane of Power BI, with the Id, Name, and Color columns and the specified data. 

Alternative Ways to Create and Manage Tables (like “Status”): 

While creating tables manually with DAX is fine for small, fixed data, Power BI offers more flexible options for dynamic or external data: 

• Import from External Sources: Connect directly to databases (SQL Server, Excel, SharePoint, etc.) to import existing status tables. This keeps data consistent and updates automatically. 

• Create in Power Query: Manually enter data or load from small files directly into Power Query. This gives you more control over data types and initial transformations. 

Choose the method that best fits your data’s complexity, how often it changes, and your existing data setup. 

2. Establish Table Relationships 

Relationships in Power BI are vital for the Task Stream PRO visual to work. They allow your tables to connect and enable proper data interpretation across your hierarchy. 

1. In Power BI Desktop, go to the Model view. 

2. Create Relationships: 

• Milestones to Milestones Tasks: Drag Id from Milestones to Milestone Id in Milestones Tasks. Ensure it’s a One-to-Many (*) relationship with single-direction filtering (from Milestones). 

• Status to Milestones Tasks: Drag Id from Status to Status in Milestones Tasks. Ensure it’s a One-to-Many (*) relationship with single-direction filtering (from Status). 

3. Configure Data Fields in the Task Stream PRO Visual 

Once your tables are set up and related, add the Task Stream PRO visual to your report and map your data fields. 

1. In Power BI Desktop, go to the Report view. 

2. Import the Task Stream PRO visual if you haven’t already (Home -> Visuals -> From a file). 

3. Add the visual to your report canvas. 

4. With the visual selected, drag the following fields from your tables into the visual’s Data pane: 

Milestone Fields: 

• Milestones.Id  

• Milestones.Name  

• Milestones.Order  

• Milestones.Completed  

Task Fields: 

• Milestones Tasks.Id  

• Milestones Tasks.Name  

• Milestones Tasks.Milestone Id 

• Milestones Tasks.Status 

• Milestones Tasks.Order  

• Milestones Tasks.Level 

• Milestones Tasks.Parent 

• Milestones Tasks.Finish Date 

• Milestones Tasks.Effort 

Status Fields: 

• Status.Id 

• Status.Name 

• Status.Color 

After assigning fields, the Task Stream PRO visual will display your project hierarchy. You can then customize its appearance as needed. 

Final Thoughts 

Transform Your Data, Transform Your Insights 

Clean and well-structured data is essential to getting the most out of the Task Stream PRO – Process Flow Chart visual. It ensures your reports are accurate and helps you navigate project hierarchies with ease. 

Built to Work with Any Task Management Tool 

While this example uses Microsoft Planner, Task Stream PRO is highly versatile and works seamlessly with other task management platforms, giving you the flexibility to adapt it to your workflow. 

See What Your Data Can Really Do with Softcial 

At Softcial, we’re passionate about turning raw data into meaningful insights. Task Stream PRO is just one of the powerful custom visuals we’ve created to elevate project tracking and visibility. Explore our full range and discover new ways to unlock the value of your data. 

🔗 Explore more: 

🌐 softcial.com | 📰 Newsletter | 📅 Book a session