Complex Excel Data Restructuring

I’m sitting on a folder full of Excel workbooks whose columns are currently aggregated by week. That setup no longer works for our reporting: some of those fields must be broken out to daily granularity, others have to stay weekly, and a third group actually needs to recalculate dynamically based on values in the newly-created daily columns. Because every file follows the same broad layout but with small inconsistencies, I need a robust, repeatable solution rather than hours of manual copy-and-paste.

Here’s the core of what I’m after:

• A script, macro, or Power Query routine that scans each workbook, converts only the specified columns to daily data points (I’ll supply the exact mapping), keeps designated columns untouched at weekly level, and then builds the dependent columns according to simple formulas I’ll also provide.
• Output files that mirror the original naming conventions so they can drop straight back into our pipeline.
• A short read-me so that a non-developer on my team can rerun the process when new workbooks arrive.

Whether you use native Excel functions, VBA, Python + pandas, or an AI-driven approach is entirely up to you—the end result just needs to be accurate and runnable on a standard Windows machine with Office 365. If this sounds like the sort of data wrangling you enjoy, let’s talk specifics and get these files cleaned up.