Best practice to manage semi-static import data?

Question

Hi,

Context: Using Power Query in Excel to pull in data from an external application.

I perform daily data imports to Excel using Power Query. The data contains nested records and lists that needs to be expanded to find relevant values. New data fields are introduced over time, and for this reason I have a function to dynamically analyse imported data, to identify which fields that need to be expanded.

Since the data model is semi-static, it means that 99% of my input data analysis results in the same meta data table, and each data import takes longer. So essentially, I want to cache my meta data table in order to prevent computation for every data import. Using something like table.buffer does not work in this context as the meta data table is consumed by multiple other queries, resulting in calling the meta data query multiple times.

To improve efficiency I have defined a work-flow for this semi-static data:

1. Import & process external data using Power Query
2. Store this meta data in table A in Excel
3. Creates another table B in Excel that dynamically copies data from table a using excel formulas
4. Reads the meta data from table B in to Power Query for each new data import

This way I can train my data model at the beginning of the day. However, this solution is very primitive.

Is there any better way to do this? How do you manage your semi-static data?

Best Regards

//Jimmy