I work with Syspro (an ERP system) which sits on top of an SQL Database. It records all of our company's sales information, and I use PowerBI to tie together multiple tables and create reports for different departments. I currently have 1 main model, which I adjust visuals for different end users. As the number of reports has grown, I am struggling to keep changes consistent throughout all my models. I make my data transformations in Power Query, which is great, until I need to make the same change across 10+ different files.
I'm hoping someone has advice on how to create a sustainable method of data transformations rather than using Power Query. My initial thought was using SQL to transform my data, and then linking all my models to that transformed data. I would store the transformed data in Azure. That way, when I need to make a change (say adding a calculated column), I only have to make the change in SQL and all my PowerBI files are automatically updated the next time they refresh. One requirement is that wherever this transformed data is stored, I need to be able to schedule refreshes through power automate. Using SQL is just my initial idea though, and I'm interested in hearing other's opinions on what works best.