Automation in Power BI

I would recommend completing all of the Microsoft training materials for Power BI.  The approach you should take depends on a lot of factors.  Nobody is going to be able to tell you the best strategy to take without a long back-and-forth conversation that goes over what your data sources are, how often you need the data refreshed, etc.  

It will take only a few days if you do everything back-to-back, or maybe a couple weeks if you do one or two lessons a day until you are done.  It is highly worth it.

Getting data from various sources:
https://learn.microsoft.com/en-us/training/paths/prepare-data-power-bi/


Managing your data model and automatic refresh etc.
https://learn.microsoft.com/en-us/training/paths/manage-secure-power-bi/

Truth be told you should probably just complete all of the training materials which covers these two and then an additional two major topics, data modeling and designing reports.

https://learn.microsoft.com/en-us/training/courses/pl-300t00

///Mediocre Power BI Advice, but it's free///

Hi rscott79 , Thank you for reaching out to the Microsoft Community Forum.

Start by connecting your data sources, whether that’s a SQL database, Excel files or APIs, using Power BI Dataflows or Fabric Data Pipelines. These tools let you automate your data preparation and transformation steps in the cloud, so Power BI can refresh your dataset on a schedule. If your data lives on a company server, you’ll also need an on-premises data gateway to securely connect it to the Power BI service.

Once your dataflows or pipelines are set, publish your Power BI report to the Power BI Service. There, you can configure scheduled refresh (for example, hourly or daily) or if you need data that’s always current, switch your dataset to DirectQuery mode or use streaming datasets for real-time updates. Your visuals will then stay in sync automatically without you manually reloading or rebuilding anything.

If you’re building multiple reports, you can streamline updates and version control using deployment pipelines or even automate refreshes and report creation with the Power BI REST API or PowerShell scripts. This helps scale your setup from a single dashboard to a full automated reporting environment.

When all of that is in place, your Power BI workspace essentially runs itself, pulling new data, transforming it and keeping dashboards live and up to date for your company. You’ll still design visuals once, but after publishing, everything else happens automatically.

On-premises data gateway - Power BI | Microsoft Learn

Data refresh in Power BI - Power BI | Microsoft Learn

Real-time streaming in Power BI - Power BI | Microsoft Learn

Overview of Fabric deployment pipelines - Microsoft Fabric | Microsoft Learn

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn

Introduction to Dataflows and Self-service Data Prep - Power BI | Microsoft Learn

Creating a Dataflow - Power BI | Microsoft Learn

Develop solutions with dataflows - Power BI | Microsoft Learn