Looking for suggestions/recommendations
We currently have a report that has a large no. of metrics, across various areas of the business. The report is largely manually compiled. For many metrics, the data is sourced from some business system, then complex calcs/transformations are performed in Excel to get the metric, which is then manually 'plugged into' the report. The report is presented using Power BI
We want to automate the extraction/calculation process as much as we can. Both to reduce manual effort as well as to remove potential for errors/manipulation that can happen due to the Excel calcs.
Because the Excel transformations are so complex, we need to somehow keep the logic that's in the spreadsheets, but want to, as far as possible, secure the source data/calcs from human error/manipulation.
I think we need to somehow integrate the Excel data extraction/transformation logic into Power BI, and, as far as possible, remove any manual intervention required. And also remove any potential for unauthorised users to change source data or calculations. I'm not sure of the best way to go about this, so I'm looking for suggestions.
Thank you