How would you handle a monthly report you currently rebuild by hand?

Move the import and cleanup into Power Query. Every transformation is recorded as a step and replayed on refresh, so the rebuild becomes a single Refresh All.

If the source is one file per month, use Get Data → From Folder. Power Query applies one transformation to every file and appends the results, so next month's file is picked up automatically.

Load the result to a Table or the Data Model, and build pivots on top. This is usually a better answer than a macro because it is refreshable and does not require a macro-enabled workbook.