Report on Planner Data in Power BI

Introduction

This tutorial guides you through the process of connecting to and modeling Microsoft Planner data in Power BI. We'll explore how to transform your data for better insights, create calculated columns, and visualize tasks effectively. This process is not only important for data management but also enhances your reporting capabilities and allows for automation with tools like Power Automate.

Step 1: Get Your JSON File and Path

• Locate your Planner data in JSON format.
• Ensure you have the correct path to the JSON file, especially if it's stored on SharePoint.
• Confirm the file permissions to allow Power BI access.

Step 2: Connect to the File and Set the Path to SharePoint

• Open Power BI Desktop.
• Navigate to Home > Get Data > Web.
• Enter the SharePoint path to your JSON file.
  • Example format: https://yourorganization.sharepoint.com/sites/yoursite/yourfile.json
• Click OK to connect.

Step 3: Transform the Data in Power Query

• Open the Power Query Editor.
• Use the Transform tab to:
  • Split the Assignees, Subtasks, and Color Flags into separate columns for easier filtering.
  • Remove unnecessary columns that do not contribute to your analysis.
• Apply transformations like changing data types to ensure accuracy.

Step 4: Model the Data

• Navigate to the Model view in Power BI.
• Create relationships between the different tables if applicable.
• Ensure that your data model supports the questions you want to answer with your reports.

Step 5: Create Calculated Columns

• In the Data view, create calculated columns for:
  • Assignees

    Assignee = [YourAssigneeColumn]
    

  • Flags

    Flags = [YourFlagsColumn]
    

  • Subtasks

    Subtask = [YourSubtaskColumn]
    

• Use the Modeling tab to define these columns clearly.

Step 6: Visualize the Tasks

• Switch to the Report view.
• Add a Table Visual to your report.
• Drag relevant fields such as tasks, assignees, and subtasks into the table.
• Use icons to represent task status visually. You can find Unicode IDs for icons here.
• Customize the visual with filters based on color flags and assignees for better insights.

Step 7: Schedule Refresh and Automate

• Set up a scheduled refresh in Power BI to keep your data updated.
• Use Power Automate to create workflows that can trigger actions based on the data changes in Planner.
• Explore automating notifications or updates to assignees through Teams or Outlook.

Conclusion

By following these steps, you can effectively connect to and analyze your Microsoft Planner data in Power BI. This setup not only enhances your data visualization but also allows for automation, making your project management more efficient. Consider exploring additional resources linked in the video description to deepen your understanding and capabilities with Power BI.