Import Microsoft Planner Data into Power BI Using Power Automate and SharePoint

[anmolmalviya05]

In this guide, we’ll cover how to bring task data from Microsoft Planner into Power BI for analysis, using Power Automate and SharePoint as intermediaries. Since Power BI currently lacks a direct Planner connector, Power Automate lets us fetch the data from Planner and save it to SharePoint as a JSON file, enabling Power BI to access it on a scheduled basis.

Prerequisites

Before you begin, ensure you have:

1. Access to Microsoft Planner – to view and retrieve Planner task data.
2. Power Automate License – access to create and run Power Automate flows.
3. SharePoint Access – a SharePoint site to store JSON files. Using SharePoint simplifies the setup, as it doesn’t require a premium Power BI or Power Automate license.
4. Power BI Desktop – to set up and configure the JSON data connection and publish the report.
5. Microsoft 365 Account – for authentication, as we’ll be connecting Power BI to SharePoint through your organization’s Microsoft 365 credentials.
   
   

Step 1: Setting Up the Power Automate Flow

1. Access Power Automate: Go to Power Automate and create a Scheduled Cloud Flow.
   
   
   Set it to run on a periodic schedule, such as daily, to ensure your data stays updated.
   
   
2. Add Required Actions:
• List Tasks Action: Search for “List tasks” in Power Automate, select the Planner group and plan to capture all tasks, not just those assigned to you. 
  
  Select Group ID and Plan ID from drop down.
  
  
  
• Create File Action:
  Choose a SharePoint folder and save the file with a “.json” extension (e.g., Planner_Tasks.json). JSON allows easy data storage and access for Power BI.
  
  Create a new action and select create file from sharepoint group.
  
  
  Enter site address, folder name, file name ( Planner_tasks.json) and in file content section search for “body”.
  
  
  
3. Configure Overwrite Settings: In Create File settings, disable Allow Chunking to overwrite the file on each run. This ensures the JSON file contains only the most recent data.
   
4. Test the Flow: Save and manually run the flow once to create and verify the JSON file’s location and structure in SharePoint.

Step 2: Connecting Power BI to SharePoint JSON Data

1. Download the JSON File (First-Time Setup): Download the initial JSON file to configure the data structure in Power BI.
2. Load JSON in Power BI: Open Power BI Desktop, go to Get Data > JSON, and select the downloaded file. This sets up JSON data formatting for Power BI.
3. Update File Path to SharePoint:
• In SharePoint, navigate to the file, and copy the file path.
• Replace the local path in Power BI with this SharePoint URL, ensuring Power BI points to SharePoint for future refreshes.

Step 3: Transforming and Customizing Data in Power BI

• Expand and Transform Data: Expand JSON columns to access fields like task names, due dates, and assignee IDs. If fields are missing, make sure they are selected during transformation.
• Add Custom Columns: Create calculated columns for insights, such as a "Late Task" flag to identify overdue tasks, by writing custom logic in Power BI’s Power Query.

This solution offers an effective workaround for integrating Microsoft Planner data into Power BI, providing teams with actionable insights on task management without premium licensing.

Conclusion

By combining Microsoft Planner, Power Automate, SharePoint, and Power BI, you’ve established a powerful workflow for tracking and analyzing task data. This solution efficiently bypasses the current lack of a direct Planner-to-Power BI connector, allowing seamless data refreshes for up-to-date reporting. With Power BI’s visualization capabilities, you can turn your Planner data into valuable insights, enhancing your project tracking and decision-making. This setup also offers flexibility: you can expand it by integrating additional data sources or creating more sophisticated data transformations.

[Seb2441]

Great article, thank you for that!    been wanting to do this for a long time. 

Couple questions, 

1- In step 2.3, to "Replace the local path in Power BI with this SharePoint URL"... I am able to copy the URL, but having difficulties in finding where ot replace the local path in PowerBI. 

2- Any recommendation to get a table with user name so i can display name versus Azure ID....

Any help would be appreciated!!!   Thank you again!

[MikeAinOz]

This looks interesting, I've tried this before with using Logic Apps, and I'm ready to have another go with this way of doing it! This is my previous attempt https://github.com/ParadigmBI/PlannerDataResources

@Seb2441 I'm interested on anyone working with this!