Skip to main content

Build an Overtime Timesheet App in Power Apps — Part 15: Build the Power BI Overtime Report

· 29 min read
Jagdish Kumawat
Founder @ Dewiride

The app's Reports screen answers "how much overtime this month?". Approvers and payroll also want to look across months, by department and by person. This part builds a Power BI report on the app's SharePoint lists, entirely in the browser, with row-level security: each approver sees their own departments, and payroll sees everything.

This is Part 15 of a 21-part series.

  1. Part 1: Plan the App and Set Up the Demo Organization
  2. Part 2: Create the SharePoint Site and Lists
  3. Part 3: Keep Overtime Private with SharePoint Permissions
  4. Part 4: Create the Solution, Environment Variables and Canvas App
  5. Part 5: Build the Home Screen
  6. Part 6: Build the Log Overtime Screen
  7. Part 7: Build the My Overtime Screen
  8. Part 8: Build the Approval Flow for Teams and Outlook
  9. Part 9: Show Approvers What's Waiting
  10. Part 10: Build the Departments Screen
  11. Part 11: Build the Reports Screen
  12. Part 12: Import the Old Excel Timesheet
  13. Part 13: Create the Monthly Payroll Workbook with Office Scripts
  14. Part 14: Create the Payroll Workbook from the App
  15. Part 15: Build the Power BI Overtime Report (you are here)
  16. Part 16: Share the App and Add It to Microsoft Teams
  17. Part 17: Test Every Role and Department (coming soon)
  18. Part 18: Build a Setup Flow That Creates the Lists (coming soon)
  19. Part 19: Run the Solution Checker and Export the Solutions (coming soon)
  20. Part 20: Install the App in a New Environment (coming soon)
  21. Part 21: Recap, Production Checklist and Next Steps (coming soon)

Part 11 gave approvers and payroll a Reports screen in the app, one month at a time. This part builds the same figures in Power BI, in a workspace called Overtime Timesheet: a semantic model of the OvertimeEntries and Departments lists, and a report with cards, a month slicer, two charts and a table. Everything is done in the Power BI service, in the browser, so it works on a Mac too. Each generic task has its own guide; this part gives the values for this app, and why.

What You'll Build​

The report, as payroll sees it:

The Overtime Report with the title Approved overtime, cards Total hours 74.00, Entries 19 and People 5, a Month slicer with 2026-10 and 2026-11, a bar chart of Total hours by Department with Emergency Department 44.50 and Radiology 29.50, a bar chart of Total hours by Person, and a table of WorkDate, Person, Department and Hours with a total of 74.00

How It Works​

The decisions behind it:

  • Approved twice, as everywhere else. Like the Reports screen and the payroll workbook, the report counts an entry only when both its Status and its Approval Status say Approved. Power Query filters the rest out before they reach the model.
  • Only what the report needs. Six columns of each entry: the date, hours, department, person and month. Reasons, comments and times stay in SharePoint.
  • Approvers come from the Departments list. A Department approvers table has one row per department and approver, from the list's Approvers column. The Approvers role keeps the rows whose email is the signed-in user's, and relationships carry that filter to Departments and on to the entries.
  • Payroll sees everything. The Payroll role has no rules, which Microsoft's security planning article says provides access to all rows of all model tables.
  • Groups, not people. Overtime Timesheet Users is the Approvers role's member and the workspace's Viewer; Overtime Payroll is the Payroll role's member. Staff who approve nothing see nothing.
  • Refreshed twice a day. The model refreshes at 07:00 and 13:00 UK time, so approvals made in the morning show in the afternoon.
  • What it costs. Power BI is the one paid part of the series. Every viewer needs a Power BI Pro license, unless the workspace is on a Premium or Fabric F64 or larger capacity, as Part 1's cost section explains. Microsoft's Power BI pricing page lists Pro at $14.00 per user per month, paid yearly, included in Microsoft 365 E5. The app's Reports screen needs no Power BI license.

Prerequisites​

  • Part 14 completed.
  • A Power BI Pro or Premium Per User license, or a Power BI trial, for you and for everyone who'll view the report.

Step 1: Create the Workspace​

At app.powerbi.com, select Workspaces > New workspace (How to Create a Workspace in Power BI). Name it Overtime Timesheet, with the description Approved overtime from the Overtime Timesheet app, for approvers and payroll.:

The Create a workspace panel with the Name Overtime Timesheet, This name is available, and the Description Approved overtime from the Overtime Timesheet app, for approvers and payroll.

Under Workspace type, keep Power BI Pro:

The Workspace type section with Power BI Pro selected and outlined in red

Select Apply. The workspace opens, empty:

The Overtime Timesheet workspace header with its description and the New item, New folder, Import and Migrate buttons

Step 2: Get the Two Lists​

Select New item > Semantic model > Get Data, then SharePoint Online list (How to Get Data from a SharePoint List in Power BI in the Browser):

The Choose data source page with SharePoint Online list outlined in red among the new sources

Fill in the connection:

SettingValue
Site URLThe Overtime Timesheet site from Part 2
Implementation2.0
View modeAll - Retrieve all columns from a SharePoint List
ConnectionA connection for the site signed in as the app's owner, or Create new connection with Organizational account

The Connect to data source page with the Site URL of the Overtime Timesheet site, Implementation 2.0, View mode All, and Connection set to Create new connection, with Authentication kind Organizational account and the signed-in Admin User

In my tenant, Power BI picked a connection I already had for the site, signed in as the app's owner, and I kept it. The picture shows the other choice, Create new connection, which you'll see the first time. Either way, the connection reads the lists as the app's owner, like the flows, so the model gets every entry and the roles decide who sees which.

Select Next, tick Departments and OvertimeEntries among the site's 22 lists and libraries, and select Transform data:

The Choose data page with Departments and OvertimeEntries ticked and outlined in red, and a preview of OvertimeEntries with WorkDate, StartTime, EndTime, BreakMinutes, Hours and DepartmentId

Step 3: Keep the Approved Entries​

Select the OvertimeEntries query. Choose columns keeps eight of its columns (How to Choose Columns in Power Query): WorkDate, Hours, DepartmentId, Department, EmployeeName, Status, WorkMonth and Approval Status:

The Choose columns dialog with WorkDate, Hours, DepartmentId, Department, EmployeeName and Status ticked, and StartTime, EndTime, BreakMinutes, Reason, EmployeeEmail and ApproverComment unticked

The rest of the list, with WorkMonth and Approval Status ticked, and Reason, EmployeeEmail, ApproverComment, DecisionBy, DecisionOn, Title, Approver Comments and Color Tag unticked

Approval Status is the content approval status from Part 3. Filter rows in Advanced mode keeps the entries approved twice (How to Filter Rows by Two Conditions in Power Query):

The Filter rows dialog in Advanced mode: keep rows where Status equals Approved and Approval Status equals Approved

19 entries were left: 11 in October, 46.25 hours, and 8 in November, 27.75 hours, the same as the Reports screen. With only approved rows, the two status columns say Approved everywhere, so Remove columns drops them (How to Remove Columns in Power Query):

The Power Query ribbon with Remove columns outlined in red, and the OvertimeEntries query with its Status and Approval Status columns selected

Then set two types (How to Change a Column's Data Type in Power Query): WorkDate from Date/Time to Date, and DepartmentId to Whole number, because it's matched with the departments' ID:

The WorkDate column's type menu with Date outlined in red

The query now has six columns and 19 rows:

The OvertimeEntries query with WorkDate as dates, Hours, DepartmentId, Department, EmployeeName and WorkMonth, 19 rows from 07/11/2026 to 03/11/2026, and a Table.TransformColumnTypes formula

The Applied steps of OvertimeEntries: Source, Navigation 1, Choose columns, Filtered rows, Removed columns and Changed column type

Step 4: Build the Department Approvers Table​

The Departments list's Approvers column holds several people per department. The role needs them one per row, so they get their own query. Right-click Departments under Queries and select Duplicate (How to Duplicate a Query in Power Query):

The shortcut menu of the Departments query with Duplicate outlined in red, and the query's Department, Approvers as [Table], IsActive, Title and Color Tag columns

Duplicate, not Reference: the next steps remove Approvers from Departments, and a reference would lose it too. Rename the copy Department approvers (How to Rename a Query in Power Query):

The Query settings Properties with the Name Department approvers outlined in red

In Department approvers, choose the columns Approvers and ID, then expand Approvers to its email field, without the prefix (How to Get the Email from a SharePoint Person Column in Power Query):

The Department approvers query with Approvers as [Table] and ID, and the expand box with only email ticked and outlined in red, and Use original column name as prefix unticked

Rename email to ApproverEmail and set it to Text (How to Rename a Column in Power Query). Four rows: Olivia Bennett for departments 1 and 6, Daniel Brooks for 2, and the app's owner for 5:

The Department approvers query with ApproverEmail and ID: olivia.bennett 1, daniel.brooks 2, admin 5 and olivia.bennett 6

Finally, Departments keeps Department, IsActive and ID:

The Departments query with Department, IsActive and ID: Emergency Department 1, Radiology 2, Finance 5 and Day Surgery 6, all TRUE

Step 5: Create the Semantic Model​

Select the arrow next to Create a report and choose Create a semantic model only (How to Create a Semantic Model Without a Report in Power BI):

The arrow beside Create a report opened, with Create a semantic model only outlined in red

Name it Overtime Timesheet, in the Overtime Timesheet workspace, and select Create:

The Create new semantic model dialog with the Overtime Timesheet workspace selected and the name Overtime Timesheet

The model editor opens with the three tables:

The model editor with the Home ribbon, and the OvertimeEntries, Departments and Department approvers tables in the diagram

Step 6: Relate the Tables​

Manage relationships showed one relationship Power BI had made by itself: OvertimeEntries (Department) to Departments (Department), by the department's name:

The Manage relationships dialog with one relationship, OvertimeEntries (Department) to Departments (Department), many to one, Active

An entry's Department is a snapshot of the name when it was logged (Part 2), and Part 10 lets admins rename departments. DepartmentId doesn't change, so I edited the relationship to DepartmentId and ID, Many to one, Single (How to Create a Relationship Between Two Tables in Power BI):

The Edit relationship dialog from OvertimeEntries, DepartmentId, to Departments, ID, with Many to one (*:1) and Single

Then a new relationship from Department approvers (ID) to Departments (ID). Power BI filled in One to one (1:1) and Both, because each department had one approver that day, and offered no Apply security filter in both directions (Fixed: Apply Security Filter in Both Directions Is Missing in Power BI):

The relationship's Cardinality One to one (1:1) and Cross-filter direction Both, with Make this relationship active ticked and no security filter checkbox

A department can have several approvers, so set Many to one (*:1), Both, and tick Apply security filter in both directions, which carries the role's filter from the approvers to Departments (How to Make a Relationship Filter in Both Directions in Power BI):

The New relationship dialog from Department approvers, ID, to Departments, ID, with Many to one (*:1), Both, and Apply security filter in both directions ticked

The two relationships:

The Manage relationships list with Department approvers (ID) to Departments (ID) with a double arrow, and OvertimeEntries (DepartmentId) to Departments (ID) with a single arrow, both Active

The model diagram with OvertimeEntries joined to Departments, many to one, and Department approvers joined to Departments with arrows both ways

Step 7: Add the Measures​

Select OvertimeEntries and New measure, three times (How to Create a Measure in Power BI in the Browser):

OvertimeEntries → measures
Total hours = SUM(OvertimeEntries[Hours])
Entries = COUNTROWS(OvertimeEntries)
People = DISTINCTCOUNT(OvertimeEntries[EmployeeName])

The formula bar with Total hours = SUM(OvertimeEntries[Hours])

Step 8: Add the Roles​

Select Manage roles (How to Add Row-Level Security Roles in Power BI in the Browser). The Approvers role filters Department approvers, in the DAX editor:

Approvers → Department approvers
[ApproverEmail] = USERPRINCIPALNAME()

USERPRINCIPALNAME() is the signed-in user (How to Get the Signed-In User in Power BI with USERPRINCIPALNAME). The rule keeps that person's approver rows; the relationships then keep their departments, and those departments' entries.

The Manage security roles dialog with the Approvers role, the Department approvers table selected, and the rule [ApproverEmail] = USERPRINCIPALNAME()

The Payroll role gets no rule at all, so it sees every row:

The Manage security roles dialog with Successfully applied role changes., the Approvers and Payroll roles, Payroll selected with no rules

The model's code, in one place:

A code window titled Overtime Timesheet semantic model with the measures Total hours = SUM(OvertimeEntries[Hours]), Entries = COUNTROWS(OvertimeEntries) and People = DISTINCTCOUNT(OvertimeEntries[EmployeeName]), and the Approvers role rule [ApproverEmail] = USERPRINCIPALNAME()

Step 9: Build the Report​

Select New report in the model editor (How to Create a Report from a Semantic Model in Power BI) and add:

VisualFieldsGuide
Text boxApproved overtime, 24 point, boldText box
CardTotal hours, Entries, PeopleCard
SlicerWorkMonthSlicer
Clustered bar chartDepartment of Departments, Total hoursBar chart
Clustered bar chartEmployeeName, Total hoursBar chart
TableWorkDate, EmployeeName, Department, HoursTable

The department chart uses Department from the Departments table, which the roles filter, rather than the entry's snapshot of the name.

Three things went wrong on the way, each with its own fix:

The table showed Year, Quarter, Month and Day instead of WorkDate:

The table visual with Year, Quarter, Month and Day columns before EmployeeName, Department and Sum of Hours

In the Columns well, the field's menu switches from Date Hierarchy to WorkDate (Fixed: Power BI Table Shows Year, Quarter, Month and Day Instead of the Date):

The WorkDate field's menu with WorkDate outlined in red and Date Hierarchy ticked

The fonts shrank. The new page was 1920 by 1080, and its text looked small to me, so I changed it to 1280 by 720:

The Canvas settings with Type 16:9 and the Size list open, with 1280 x 720 (HD) outlined in red

With data labels on, the department chart then warned that it had reduced its fonts, on the axes, legend and labels:

The chart's Format visual settings with warning triangles next to Y-axis, X-axis, Legend and Data labels, which is On and outlined in red

The visuals were too small for their labels. I set the page back to 1920 x 1080 and made every visual one and a half times bigger with exact sizes (Fixed: "Data Label Font Reduced" on a Power BI Visual):

VisualWidth × heightPosition (horizontal, vertical)
Text box1170 × 8030, 20
Card1170 × 16530, 120
Slicer660 × 1651230, 120
Department chart930 × 37530, 300
Person chart900 × 375990, 300
Table1860 × 37530, 690

The labels rounded 44.50 to 45. Total hours had the General format. In the model, its Formatting became Decimal number with 2 decimal places (Fixed: Power BI Data Labels Show 45 Instead of 44.50):

The Formatting settings of Total hours with Format Decimal number, Percentage format No, Thousands separator No and Decimal places 2

Finally, Rename for this visual gives the readers' names (How to Rename a Field in One Power BI Visual): EmployeeName becomes Person in the person chart and the table, Sum of Hours becomes Hours, and WorkMonth becomes Month in the slicer:

The person chart's Y-axis well with EmployeeName and its menu, Rename for this visual outlined in red

Rename the page Overtime (How to Rename a Page in a Power BI Report), and save the report as Overtime Report in the Overtime Timesheet workspace (How to Save a Report in Power BI in the Browser):

The Save your report dialog with the Overtime Timesheet workspace and its semantic model, and the name Overtime Report

The report shows 74.00 hours from 19 entries by 5 people, as in the picture at the top. Selecting 2026-10 in the slicer gives October: 46.25 hours from 11 entries by 4 people, with 30.50 for the Emergency Department and 15.75 for Radiology, the same as the Reports screen:

The Overtime Report with 2026-10 selected: Total hours 46.25, Entries 11, People 4, Emergency Department 30.50 and Radiology 15.75, and the October entries in the table

Step 10: Schedule the Refresh​

The workspace now holds the model and the report:

The Overtime Timesheet workspace list with the Overtime Report report and the Overtime Timesheet semantic model, refreshed on 21/11/2026

Open the model's More options (...) > Settings (How to Schedule a Refresh of a Power BI Semantic Model):

The semantic model's More options menu with Settings outlined in red, among Security, Rename, Open semantic model and Refresh history

Under Gateway and cloud connections, the SharePoint source maps to the connection chosen in Step 2; no gateway is needed, because the data is in the cloud:

The Gateway and cloud connections settings: no gateway needed, the gateway switch Off, and the SharePointList data source mapped to the contoso SharePoint connection

Under Refresh, set the time zone to (UTC) Dublin, Edinburgh, Lisbon, London, turn the schedule On, Daily at 7:00 AM and 1:00 PM, keep the owner notified of failures, and Apply:

The Refresh settings with Last refresh succeeded, the time zone (UTC) Dublin, Edinburgh, Lisbon, London, the schedule On, Daily at 7 00 AM and 1 00 PM, Semantic model owner ticked for notifications, and Apply

Step 11: Give Approvers and Payroll Their Roles​

I tested the roles before adding anyone to them. On the model's Security page, Test as role with Approvers showed blanks for me, correctly: I approve only Finance, which has no approved overtime (How to Test Row-Level Security in Power BI):

The Overtime Report under Now viewing as Approvers, with blank cards, empty charts and an empty table

Viewing as Olivia Bennett instead:

The View as panel on Select person with Olivia Bennett and an Apply button

gave This user is not configured for any row-level security roles: the roles had no members yet (Fixed: "This User Is Not Configured for Any Row-Level Security Roles" in Power BI).

On the Security page, add the groups (How to Add Members to a Row-Level Security Role in Power BI). Approvers gets Overtime Timesheet Users, the group of all the app's staff from Part 1:

The Row-Level Security page with Approvers (0) selected and Overtime Timesheet Users typed, with the group suggested below

Approvers (1) with Overtime Timesheet Users in its member list

Payroll gets Overtime Payroll:

Payroll (0) selected with Overtime Payroll typed and suggested

Payroll (1) with Overtime Payroll in its member list, and Approvers (1)

Select Save. Payroll officers are in both groups, and roles add up, so they see everything.

The readers also need the workspace. In Manage access, add Overtime Timesheet Users as Viewer (How to Give Someone Viewer Access to a Power BI Workspace). Viewer is also the only workspace role row-level security applies to:

The Add people panel for Overtime Timesheet with Overtime Timesheet Users and the role Viewer

The Manage access list with Admin User as Admin and Overtime Timesheet Users as Viewer

Step 12: Test Each Role​

Back on the Security page, Test as role, then Now viewing as each person.

Olivia Bennett, the Emergency Department's approver, sees only her department: 44.50 hours from 11 entries by 3 people:

The report viewed as Olivia Bennett: Total hours 44.50, Entries 11, People 3, only Emergency Department in the department chart, Aisha Khan, Ben Harris and Admin User in the person chart, and only Emergency Department entries in the table

Daniel Brooks, Radiology's approver, sees Radiology: 29.50 hours from 8 entries by 4 people, his own 2.50 hours included, because the report is by department:

The report viewed as Daniel Brooks: Total hours 29.50, Entries 8, People 4, only Radiology in the department chart, and Maya Patel, Aisha Khan, Admin User and Daniel Brooks in the person chart

His own entries are still approved by his manager, as Part 8 set up; the report only shows them.

Leo Martin, the payroll officer, sees everything: 74.00 hours from 19 entries by 5 people:

The report viewed as Leo Martin: Total hours 74.00, Entries 19, People 5, both departments in the department chart and all five people in the person chart

Common Mistakes​

MistakeWhat happensDo this instead
Loading every entryPending and rejected hours appear in the totalsFilter on Status and Approval Status in Power Query
Relating entries to departments by nameA renamed department breaks the matchDepartmentId to ID
Reference instead of Duplicate for the approversThe query loses Approvers with DepartmentsDuplicate
Accepting a one-to-one approvers relationshipNo security checkbox, and the rule stops at the approversMany to one, Both, security filter ticked
Making readers Members of the workspaceRow-level security doesn't apply to themViewer
Adding people instead of groupsEvery joiner needs a change in two placesThe app's groups

Troubleshooting​

SymptomLikely causeFix
Totals differ from the Reports screenThe filters differBoth Status and Approval Status equal Approved
An approver sees every departmentThey're a workspace Admin, Member or ContributorMake them a Viewer
An approver sees nothingTheir email isn't in the department's ApproversCheck the Departments screen
This user is not configured for any row-level security rolesThe person isn't in a roleAdd their group
This user does not have access to the dataset or reportThe person isn't in the workspaceAdd their group as Viewer
Today's approvals are missingThe next refresh hasn't runWait for 07:00 or 13:00, or refresh now

FAQ​

Why not use Export to Power BI on the SharePoint list?​

That makes a semantic model from the one list you export. This report needs two lists shaped in Power Query first, so it starts from Get Data.

Does this need Power BI Desktop?​

No. Every step here runs in the browser, including Power Query, relationships, measures and row-level security.

Can approvers see the reasons for overtime?​

No. The model holds six columns of each entry and no reasons; the reasons stay in SharePoint, behind Part 3's permissions.

Why do I see blanks when I test the Approvers role?​

Test as role uses your own sign-in. If you approve no department with approved overtime, there's nothing to show. View the report as a specific approver instead.

Conclusion​

The Power BI side of the app is in place:

  • an Overtime Timesheet workspace and semantic model, built in the browser from the OvertimeEntries and Departments lists, with only entries approved twice;
  • a Department approvers table from the departments' Approvers, related so the Approvers role's rule reaches each approver's departments and their entries;
  • Total hours, Entries and People measures, and a report with cards, a month slicer, charts by department and by person, and a table;
  • a refresh at 07:00 and 13:00 UK time;
  • the app's groups as role members and workspace Viewers, tested as an approver of each department and as payroll.

Part 16: Share the App and Add It to Microsoft Teams shares the app with the app's groups and adds it to Microsoft Teams.

Additional Resources​

← Previous: Part 14: Create the Payroll Workbook from the App Next → Part 16: Share the App and Add It to Microsoft Teams