Building a Multi-Project Tracking Dashboard in Power BI with SharePoint and Power Automate

A request that comes up constantly from clients running several projects at once: "I want one dashboard that shows me every project's status, how far along it is, and whether it's late — without someone manually updating a spreadsheet every Monday." The data is almost always sitting in SharePoint already, because that's where project managers already track tasks. The missing piece isn't Power BI modeling skill, it's wiring SharePoint, Power Automate and Power BI together so the dashboard reflects reality the moment someone changes a status.

Why a SharePoint list is the right starting point

Project managers update task status far more often than anyone touches a formal reporting tool, and a SharePoint list is usually where that habit already lives: one row per task or project, with columns for status, owner, start/end date and percent complete. Power BI can connect to that list directly with the native SharePoint List connector, but a direct connection on its own still relies on a scheduled refresh (every 30 minutes at best on Pro) — fine for a weekly report, not fine for "did the status change five minutes ago." Power Automate closes that gap by reacting to the SharePoint change itself instead of waiting for Power BI's clock.

The three pieces

1. SharePoint list — the source of truth. One list per project, or one list for all projects with a "Project" column, plus a "Tasks" list linked by Project ID.
2. Power Automate flow — watches the list and reacts to changes.
3. Power BI report — reads either the SharePoint list directly, or an intermediate table that the flow maintains (and which Power BI then just has to read, not transform).

Step 1 — Model the SharePoint list for reporting, not just data entry

Before building anything in Power Automate, make sure the list has what a dashboard needs:

• Status as a Choice column (Not Started / In Progress / Blocked / Done), not free text — this is what powers a status chart without text cleanup in Power Query.
• PercentComplete as a Number column (0–100), ideally driven by task completion rather than typed by hand.
• DueDate and StartDate as Date columns, so Power BI can calculate "days until due" and flag overdue items with DAX instead of a manually maintained "Late?" column.
• Project and Owner as Lookup or Person columns, so the dashboard can slice and filter correctly.

If percent complete should come from a child "Tasks" list (e.g., 8 of 12 tasks done = 67%), don't ask anyone to type that number — calculate and write it with the flow in Step 2.

Step 2 — Power Automate: keep status and progress current

Build one flow per list, triggered on "When an item is created or modified" on the Tasks (or Projects) list:

1. Trigger: SharePoint — When an item is created or modified, on the Tasks list.
2. Condition (optional but recommended): only continue if the field that actually matters for reporting changed — a Status or PercentComplete field — to avoid recalculating on every edit, including ones that don't affect the dashboard (e.g., someone fixing a typo in a comment).
3. Get items: pull all tasks for that project (Project eq '<id>' as an OData filter) to recompute aggregates.
4. Compose / calculation: percent complete = tasks with Status = "Done" ÷ total tasks for that project; overall project status = "Blocked" if any task is Blocked, else "Done" if all tasks are Done, else "In Progress".
5. Update item: write the computed PercentComplete and Status back to the parent Project item in the Projects list.

This keeps the heavy logic (what counts as "blocked," how progress rolls up from tasks to project) in one place — the flow — instead of duplicated across DAX measures that every report author has to get right the same way.

Step 3 — Power BI: read the Projects list and layer in freshness logic

Connect Power BI to the Projects list (not the raw Tasks list) with Get Data → SharePoint Online List, since that's the table Power Automate already keeps rolled up and clean. Two DAX measures do most of the remaining work:

Days Until Due =
DATEDIFF(TODAY(), SELECTEDVALUE(Projects[DueDate]), DAY)

Is Late =
IF(
    SELECTEDVALUE(Projects[Status]) <> "Done" &&
    SELECTEDVALUE(Projects[DueDate]) < TODAY(),
    "Late",
    "On track"
)

Build the page around four visuals: a card for "projects late," a stacked bar of status by owner, a table with percent-complete bars per project, and a card showing the dataset's last refresh time (Last Refreshed = MAX(Projects[Modified]) reads better to a stakeholder than Power BI's own refresh timestamp, since it reflects when the data actually changed, not just when the dataset last pulled it).

Step 4 — Set the refresh so the gap feels invisible

Power Automate updates the SharePoint item in near real time, but Power BI still needs to pull that update in. Two options, same tradeoff as always:

• Scheduled refresh every 30 minutes (Pro, built into the service) — simplest, and "up to 30 minutes old" is usually acceptable for a project status dashboard that people check a few times a day.
• On-demand refresh: add a final step to the Power Automate flow that calls the Power BI REST API (POST /datasets/{id}/refreshes) right after the item update, so the dashboard catches up within a minute or two of a real status change — worth the extra setup when the project list changes often enough that "half an hour old" visibly bothers stakeholders.

What this buys you

Nobody is retyping a status into a spreadsheet, nobody is asking "is this still accurate," and the project manager's normal habit of updating SharePoint is now the only data entry the dashboard needs. The pattern generalizes past project tracking too: anywhere a SharePoint list is already the operational record — onboarding checklists, ticket queues, vendor approvals — the same trigger-condition-update-rollup flow keeps a Power BI dashboard honest without anyone maintaining it by hand.

If your team already lives in SharePoint and your Power BI reports feel perpetually out of date, this is usually a half-day build, not a project — talk to us about setting it up for your project list.