Others

How to Create a Power BI Report for Project Online Users Without Timesheets

Tauseeq Magsi Sep 27, 2026 869 views

Question details

The user needs to build a Power BI report to identify active Project Online users who have not created or submitted a timesheet for a selected reporting period.

How to Create a Power BI Report for Project Online Users Without Timesheets

Product: Power BI / Project Online
Device & OS: not provided
Scenario: Monitoring timesheet compliance and tracking missing time entries for project management reporting.
Observed behavior: The standard Project Online timesheet system only shows statuses for created, submitted, or approved timesheets, lacking a specific 'Not Created' value to easily filter missing users.

Before you start

Ensure you have Power BI Desktop installed and possess the necessary administrative or reporting permissions to access your organization's Project Online OData feed URL.

Solution 1Recommended

Use Power Query Anti-Join to Find Missing Timesheets

Since Project Online does not generate records for uncreated timesheets, you must use a 'Left Anti' join in Power Query to cross-reference your total active users against the submitted timesheet data.

To find users who have not created a timesheet, you need to use exception reporting logic. This involves pulling a list of all active resources required to log time and comparing it against the TimesheetLines entity. Any resource that exists in the active list but is missing from the timesheet list for that period has not created their timesheet.

Connect to the Project Online OData Feed

Open Power BI Desktop, click 'Get Data' > 'OData Feed'. Enter your Project Online API URL (typically https://[your_tenant].sharepoint.com/sites/pwa/_api/ProjectData) and authenticate with your organizational account.

Load Resources and Timesheets Tables

From the Navigator window, select the 'Resources', 'TimesheetPeriods', and 'TimesheetLines' entities. Click 'Transform Data' to open the Power Query Editor.

Filter the Active Resources

In the 'Resources' query, filter the data to only include active users who are required to submit timesheets (e.g., filter the 'ResourceIsActive' column to TRUE).

Merge Queries with a Left Anti-Join

Click 'Merge Queries as New'. Select your filtered 'Resources' table as the first table, and the 'TimesheetLines' table as the second. Highlight the 'ResourceId' column in both tables. Under 'Join Kind', select 'Left Anti (rows only in first)'. Click OK.

Create the Exception Report Visual

Click 'Close & Apply' to load the data into Power BI. Create a Table or Matrix visual using the resulting merged query to display the names of active resources who have no matching timesheet records for the selected period.

Use Power Query Anti-Join to Find Missing Timesheets

Refining by Date: Be sure to create a relationship between the timesheet records and your Date or TimesheetPeriods table so you can slice the exception report by specific weeks or reporting periods.

Free Microsoft Office alternative

Manage Project Data Seamlessly with WPS Office

While Power BI is excellent for complex OData connections, managing day-to-day project trackers, resource lists, and manual timesheets is often faster in a spreadsheet. WPS Office provides a free, highly compatible, and lightweight alternative to Microsoft Office, featuring a familiar UI and seamless data migration for all your project reporting needs.

1. Download and Install: Visit the official WPS Office website to download and install the free, lightweight office suite on your device.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet to open, edit, or create your project data trackers and timesheets using the familiar interface.
3. Export for Power BI: Save your manual project logs as standard .xlsx or CSV files, ensuring perfect compatibility when importing them into Power BI.

Fully compatible with Microsoft Excel (.xlsx) formats for easy Power BI importLightweight application that runs smoothly on older or budget devicesFamiliar user interface requiring zero learning curve for seamless migrationRich collection of built-in project management and timesheet templates

microsoft office alternative - wps office

Frequently Asked Questions

Why is there no 'Not Created' status in Project Online timesheets?

Project Online's database architecture only generates a record when a timesheet is initialized, saved, or submitted. If a user never starts the process, no database record exists, which is why exception reporting (finding the missing data) is required.

Which OData tables do I need to query for timesheet compliance?

To accurately map active users to specific time periods and track compliance, you will primarily need the Resources, TimesheetClasses, TimesheetPeriods, and TimesheetLines tables from the ProjectData OData feed.

Can I automate alerts for missing timesheets in Power BI?

Yes. Once you publish your Power BI exception report to the Power BI Service, you can use Power Automate to trigger automated email reminders to the specific users identified in your report as having missing timesheets.

Office Quick Solutions