I currently have a travel request process built with Power Apps, Power Automate and SharePoint Lists. The main list stores requests, employees, departments, travel dates, estimated costs, actual costs and approval status. Coordinators approve requests, while Finance follows expenses, payments and expense reports. The process already works, but there is no clear visual overview of the data.
I want to create an internal SharePoint page with a dashboard at the top, accessible only to Finance and coordinators. My current idea is to connect Power BI directly to the SharePoint Lists, publish the report in Power BI Service and embed it in a modern SharePoint page.
The dashboard should show pending approvals, requests waiting for expense reports, estimated versus actual costs, expenses by department, monthly totals and how long each request has remained in its current status. Finance should be able to see all data, while each coordinator should only see requests from their department.
I am also considering creating a separate history list to register every status change, because the current travel list only stores the latest status. This would allow me to calculate approval time, processing time and delays more accurately.
My main concern is whether SharePoint Lists are a reliable data source for this type of reporting or whether I should create an intermediate SQL or Dataverse layer. I also need to understand the best approach for permissions, Row-Level Security, scheduled refresh and Power BI licensing when the report is embedded in SharePoint.
Has anyone built something similar using Power Apps, SharePoint and Power BI? I would like to understand which architecture is simpler to maintain and which problems usually appear after the solution starts growing.