Power Query: Updating results and writing back to source

[thewinchester]

Ok, this may seem like an odd question - so hear me out please.

Currently, we work within an Government client environment which has heavy restrictions on what we can and can't use, or do. Macros are a no go, but mercifully Power Query is ok.

The business problem that drives me up the wall is having to track training assignments and completion for over 1k people, where:

• there are multiple training packages each with different time lengths;
• where some users have to do them, some don't;
• can be assigned to a user at a date and time on multiple occasions, for example if they are absent from a shift and miss it;
• a need to track the history of the multiple dates/times assigned;
• and reconcile this against data available from the LMS for audit and billing to the end client.

However, the one thing we don't have (yet, and if it even exists) is the ability to take data brought in using Power Query, edit the data result, and being able to write that back to the source file from wherever the data originally came.

Sadly - the environment and technology constraints have meant Excel has to do the job. We achieve it using 1x excel file per training package, and using other books to store common data (employee information), as well as generate rollup reports and views of all the training assigned by a user and when it was completed. Mercifully, Power Query has made that all a lot easier and faster; but other obvious solutions such as MS Access databases (either in shared or split mode) are off the table given that there's nobody else in the building who understands use of or maintenance of Access. And whilst i'd walk over hot coals for a MSSQL instance, I simply don't have the time to jump through all the hoops to do it, let alone build and maintain the front end interface for the data entry aspects.

So - the question: Is there any way that data - brought into another Excel book using Power Query - can have the resulting data displayed within the worksheet edited (i.e. not having to edit the query itself, or use Power Query to update/transform the data), and have those edits easily transferred back to the source workbook?

I honestly don't think this is, but i'm also relatively new to Power Query (learning it by necessicity), so I thought it would be wise to consult the Mr Excel hive mind to see if there's something i'm missing or have missed.

Thanks in advance.

 

[Kelew]

Hi - Did you get an answer for this? I have a similar task and looking to see if its possible. I'd love to know if you found a solution.

Thanks

 

[jdellasala]

Assuming you pull the data into WorkbookA using PQ, and then load the data to a Table, you can copy that table/worksheet to a new Workbook, however that will also pull in the query. If the end users don't have access to the source data, the query will just fail if they attempt to refresh it. If they do, you can delete the query, but the Table will remain.
Do you know where the source data is coming from? Could it be from an SQL Database or some other central data source?