How to maintain Power Query code across multiple workbooks/workstations?

Question

I'm working on automating a lot of the data reporting in the business I work at.

It's all various tables orginating from a central database and spread out across Excel workbooks.

I'm largely limited to MS office tools at the moment.

Power Query is a great deal faster than the current methods and easier to maintain.

I notice that a lot of the reporting uses the same results over and over again. As such, I can write a query and distribute to my coworkers in an ODC file or otherwise through a file server or Teams.

However, loading an ODC loads in the raw PQ code into the file.

Which means any changes made to the master query have to be manually loaded into each file.

Is there a way update PowerQuery code across multiple worksheets?

I'm trying to avoid having to write database level queries as possible. I have minimal support on it, would prefer not to freeze the system, and learning the IBM i-series is a disproportionately larger trial.

Do a set of queries and store the results as csv. Then have a template on the users machine to call the required result - less recalculating through the database. — Solar Mike
That's a good idea! Luckily now I've a start and can start learning more about databases and the IBM i-series to create better setups. — A.H.

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2021-06-17T20:11:59

Store the M code in flat files in a Onedrive synced folder. Then load the queries dynamically using Expression.Evaluate . Chris has a great article here https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

How to maintain Power Query code across multiple workbooks/workstations?

1 Answers