How to Connect Microsoft Planner to Power BI

Getting your project data out of Microsoft Planner and into Power BI lets you create dynamic, insightful reports that go far beyond Planner's native dashboards. This unlocks the ability to see the bigger picture, track trends over time, and measure project performance in ways Planner alone simply can't. This guide will walk you through the entire process, step-by-step, showing you how to export Planner data and build powerful, interactive visualizations in Power BI.

Why Bother Connecting Planner to Power BI?

You might be thinking, "Planner already has charts. Why do all this extra work?" Planner's "Charts" view is great for a quick, at-a-glance status update on a single plan. But when you need to answer more complex questions or present your project's health to stakeholders, it quickly falls short. That's where Power BI comes in.

By bringing your Planner data into Power BI, you gain several powerful advantages:

• Fully Custom Dashboards: Move beyond Planner’s fixed charts. In Power BI, you can build reports that show exactly the metrics that matter to you and your team. Visualize workload, track due dates, and monitor progress in a way that aligns with your specific goals.
• In-Depth Analysis: Go deeper than a simple task count. You can slice and dice your data by team member, bucket, label, due date range, completion status, and more. Instantly answer questions like, "Which team member has the most overdue tasks?" or "Which phase of the project has the most tasks remaining?"
• Combine Data Sources: This is a game-changer. Power BI allows you to merge your Planner data with other sources. You could combine project tasks with timesheet data from Excel, budget information from a financial system, or even customer support tickets from a CRM to get a complete 360-degree view of your projects.
• Historical Trend Analysis: Planner shows you the current state, but Power BI can show you how you got there. Track task completion rates over time, see if team velocity is increasing, and identify recurring bottlenecks month after month.
• Automated & Scheduled Refreshes: Set up your Power BI report to update automatically on a schedule. Your dashboard will always have the latest data without you having to manually rebuild it every week.

How the Planner and Power BI Connection Works

It's important to understand upfront that there is no direct, out-of-the-box connector to pull Microsoft Planner data straight into Power BI. You can't just click "Get Data," select "Planner," and have everything instantly appear. But don't worry, the most common and effective method is simple and straightforward.

The process we will follow in this guide is:

1. Export Your Planner Data: You'll use Planner's built-in function to export your entire plan into an Excel spreadsheet.
2. Connect to Excel: You'll use Power BI Desktop to connect to that exported Excel file as a data source.
3. Build Your Report: Once the data is in Power BI, you have the creative freedom to start building your custom visuals and dashboards.

More advanced users can create complex automated workflows using Power Automate to push Planner data to a SharePoint List or Dataverse and connect Power BI to that, but the Excel method is the most accessible and powerful starting point for most users.

Step 1: Exporting Your Planner Data to Excel

First, you need to get your raw data out of your Microsoft Planner plan. The process is quick and simple.

1. Navigate to the specific plan you want to analyze within the Microsoft Planner web application.
2. In the upper-right area of the screen, just below your user profile, click the three dots (...) to open the "More options" menu.
3. From the dropdown menu that appears, select "Export plan to Excel."
4. Your browser will immediately download an Excel file. The file will automatically be named after your plan (e.g., if your plan is named "Q4 Marketing Campaigns," the file will be Q4 Marketing Campaigns.xlsx).

This single Excel file contains rich information about your project. When you open it, you’ll see one worksheet, typically named "Tasks," that includes columns for Task Name, Assigned To, Progress, Priority, Bucket, Start Date, Due Date, Completed Date, Late, Labels, and more. This is the raw data we will use to build our dashboard.

Pro Tip: Where to Save Your Excel File

Before you jump into Power BI, think about where you're saving this downloaded Excel file. While you can save it on your desktop to get started, it's best practice to save it in a cloud location like OneDrive for Business or a SharePoint document library.

Why? Because Power BI Service (the online version) can connect directly to files in these locations, which is the key to setting up scheduled, automatic data refreshes later on. This single step will save you countless hours of manual work in the future.

Step 2: Connecting Your Planner Excel File to Power BI Desktop

With your Planner export saved, it's time to bring that data into Power BI Desktop. If you don't have it installed yet, you can download it for free from the Microsoft Store.

1. Open a new, blank report in Power BI Desktop.
2. In the Home ribbon at the top, click on the "Get Data" icon.
3. A common data sources window will pop up. Select "Excel Workbook" and click "Connect."
4. Navigate to wherever you saved your exported Planner Excel file and select it. Click "Open."
5. The Navigator window will now appear. This window shows you all the sheets and tables found inside your Excel file. Your planner data will likely be under a worksheet named "Tasks."
6. Check the box next to the "Tasks" table or sheet. Power BI will display a preview of the data on the right so you can confirm it looks correct.
7. You now have two options: Load or Transform Data.

For this guide, let's click "Transform Data" to ensure our data is perfect for analysis.

Step 3 (Optional but Recommended): Clean and Prepare Your Data

Raw data exports are rarely optimized for reporting. Taking a few minutes to clean up your Planner data in the Power Query Editor will make building your dashboard much easier and your final report far more accurate.

Here are a few common and highly useful transformations for Planner data:

1. Check Column Data Types

Power Query usually does a good job of guessing data types, but it's always wise to double-check. Ensure your date columns like Due Date, Start Date, and Completed Date are actually set to the "Date" or "Date/Time" type, not "Text." To change a type, right-click the column header, select "Change Type," and choose the correct format.

2. Create a "Task Status" Column

Perhaps the most valuable transformation you can make is creating a custom status column to categorize tasks. This goes beyond Planner's simple "Not started," "In progress," "Completed."

• In the Power Query Editor, go to the "Add Column" tab and click "Conditional Column."
• A dialog box will appear. Use it to build logic. For example, let's create a status for "Overdue," "Completed," and "In Progress":
• Give the new column a name, like "Task Status," and click OK.

3. Split the "Assigned To" Column

If a task is assigned to multiple people, Planner exports their names into a single cell, separated by a semicolon (,). This makes it impossible to count tasks per person accurately. Let's fix that.

• Select the "Assigned To" column.
• On the "Home" tab, click "Split Column" > "By Delimiter."
• Choose "Semicolon" as the delimiter.
• Under "Advanced options," select to split into "Rows." This is the magic step. It will duplicate the task row for each assigned user, ensuring every person gets credit for their assigned task.

Once you are happy with your data transformations, click "Close & Apply" in the top-left corner of the Power Query Editor to load your clean data into the Power BI model.

Step 4: Building Your Planner Dashboard in Power BI

Now for the creative part! With your data loaded, you can start creating visuals. Drag and drop fields from the "Data" pane on the right onto the report canvas.

Here are some powerful visual ideas to answer common project management questions:

Key Performance Indicators (KPIs)

Use Card visuals from the Visualizations pane to show high-level numbers at a glance. You can create cards for:

• Total Task Count
• Completed Tasks
• Tasks In Progress
• Overdue Tasks (filter by your custom status column)

Project Status Overview

Use a Donut chart or Pie chart to show the distribution of your tasks. Drag your "Task Status" field to both the Legend and Values fields to see a clear breakout of what's completed, overdue, and in progress.

Team Workload Distribution

Use a Stacked bar chart to see who is assigned to what. Put the "Assigned To" field on the axis and the "Task Name" (and use Count as the aggregation) on the values. This immediately shows who has the most tasks assigned to them, helping you spot potential burnout or resource gaps.

Tasks by Project Phase (Bucket)

Use a Treemap or Bar chart to visualize how tasks are spread across your Planner buckets. This helps stakeholders understand where the bulk of the work lies (e.g., "Development," "Testing," "Deployment").

Detailed Task Table

Use the Table visual to create a detailed list of specific tasks. You could create one specifically for overdue items. Apply a visual-level filter on the table to only show tasks where "Task Status" is "Overdue" and include columns like "Task Name," "Assigned To," and "Due Date."

Interactive Slicers

Add Slicers to make your dashboard interactive. Create slicers for "Assigned To," "Bucket," or "Priority" so that viewers can click a team member's name or a project phase and see the entire report filter down to just that selection.

Keeping Your Data Up-to-Date: Publishing and Refreshing

A report is only useful if its data is current. You have two main ways to keep your Power BI report synchronized with your latest Planner data.

1. Manual Refresh

This is the quick and dirty method. To update your data, you simply:

• Go back to Planner and export the plan to Excel again.
• Save the new file, overwriting the old one in the exact same location with the exact same name.
• Open your Power BI Desktop file and click the "Refresh" button in the Home ribbon.

Power BI will re-run all your query steps on the new data, and your visuals will update. This works fine for ad-hoc reports but is tedious for daily or weekly tracking.

2. Automated Scheduled Refresh

This is where the real power lies. By publishing your report to the Power BI Service, you can put updates on autopilot. This is why we saved the Excel file to OneDrive or SharePoint.

• In Power BI Desktop, click the "Publish" button on the Home ribbon and select a Workspace to publish to.
• Once published, open your web browser and go to app.powerbi.com. Navigate to your workspace.
• Find the dataset for your new report (it will have the same name), click the three dots (...) and choose "Settings."
• Expand the "Data source credentials" section. Click "Edit credentials" and sign in with your Microsoft account to grant Power BI permission to access the file in your OneDrive/SharePoint.
• Next, expand the "Scheduled refresh" section. Toggle it on.
• Choose your desired refresh frequency (e.g., Daily) and set a time. You can add multiple times per day if needed.
• Click "Apply."

That's it! Now, all you have to do is remember to periodically download the fresh export from Planner and save it to that cloud location. Power BI will handle the rest, automatically refreshing your dashboard according to the schedule you set.

Final Thoughts

Connecting Microsoft Planner to Power BI transforms your basic task lists into a rich, interactive project management dashboard. By simply exporting your Planner data to Excel and leveraging Power BI's powerful visualization capabilities, you can unlock deep insights into workload distribution, project progress, and potential bottlenecks that are impossible to see in Planner alone.

While this Excel-based method unlocks a ton of value, the process of repeatedly exporting and managing file refreshes can still feel like a manual chore. At Graphed, we created our tool to remove this friction. We focus on connecting directly to all your apps so you can use simple, natural language to ask questions and get answers. This approach helps automate the entire reporting process, giving you live dashboards so you can spend your time acting on insights, not just gathering them.