If you’re using a SharePoint list to manage tasks and actions on a project you’ll need a tool to gain insights on project performance – how the team are performing against the schedule, where resource bottlenecks might occur, any tasks that are behind schedule, and so on.
This article explains how that can be achieved by getting the data from the lists and team member profiles into Power BI, transforming it to create relevant visualisations, publishing it, and setting up a refresh schedule to ensure it remains current.
First step is to install Power BI if you haven’t already. Create a new, blank report and click ‘Get data/Online Services/SharePoint Online List’, choose the site the list is on, then the task list and ‘Transform data’ to open the Power Query window.
If your organisation has a staff table which includes weekly working hours per member get that data into Power Query too.
Remove any redundant columns from both tables, then filter out any redundant rows – perhaps tasks with a status of ‘deleted’ won’t be necessary, and only members of the team will be needed. Filtering and removing columns as the first steps tends to improve query loading and reduce the size of your Power BI file.
You can then perform transformations on the data, for instance:
- Convert any data/time columns to ‘Date only’
- Create a custom column which links to each list item, using the ID column as the variable
- Ensure the columns you’ll be creating relationships between tables with are of the same type and the data is consistent between them – lower case email addresses or whole numbers, for instance
Once you’re satisfied all and only the right data you’ll need is in the tables, close and load to leave Power Query and return to Power BI and wait for the data to be refreshed.
Save your report in a shared team location with a name that is clear – no point adding a version name to it at this stage.
Before adding any visualisations, consider theming your report to match your organisation’s branding – see Growing Trust in Power BI Reporting for guidance on this.
Add a text box at the top for the report title; and buttons for any relevant content – perhaps to the SharePoint list and project documentation site and we can start building the important stuff – visualisations the key ones for project management being:
- Gantt chart – Microsoft provide a free, minimal Gantt chart as a custom visualisation. The chart lacks more advanced features – like dependencies – which are available in MS Project. Guidance on configuring this is here: Building a Gantt Chart using Power BI while various paid-for Gantt charts are also available from the custom visual store.
- Histogram – When set beneath the Gantt chart, a histogram is invaluable for understanding where we can expect peaks and troughs in work to occur over time – and potentially shuffle tasks around to better allocate project resources. To do so, we need to understand how much resource is available across the project, how much resource each task within the project is expected to take, and the boundaries of time each task has. Once we have that data, we can use an events in progress calculation to build a histogram using a clustered column visualisation.
Other useful visualisations to include might be:
- A line graph showing tasks over time, with ‘Status’ as a legend and ‘task due’ for the x-axis
- A column chart showing tasks by category, with ‘status’ or ‘priority’ as the legend
- A bar chart showing the sum of hours completed by each team member – if the competition associated with a leader board works for your team
- A filterable table with columns for tasks (hyperlinked), who’s responsible for them, status (conditionally formatted), and date due. You could also add a tool-tip page containing the ‘Description’ and/or ‘Actions’ list, and link the table to it.
Once you’re happy with the bare-bones of a report save it again and ‘publish’ to a workspace all your team members can access. Open the workspace in the Power BI service and click ‘settings’ on the semantic model to configure a refresh schedule.
Share a link to the report with your team, ask for feedback, improve it (save old files with a version number before updating), and review it during team meetings to ensure your project stays on time and on budget.
This article is part four of the Flexible Project Management Information Handyman series:
- Flexible Project Management with SharePoint Lists and the Power Platform
- Issuing a weekly digest to each responsible person listing their upcoming and overdue tasks
- Adding sub-tasks, or actions, to your project tasks using another list and a Power Apps form
- Building a custom Power BI report to gain insights on team activities and report on progress