Dynamic Write-Back to Sync PowerPivot Tables with Date Tables created by Power Query

The following technique is a modification to Chris Webb’s excellent Power Query article located at: http://cwebbbi.wordpress.com/2013/11/19/generating-a-date-dimension-table-in-power-query/

I hope that I am not stepping on something that Chris has already done but not published. Anyway, in order to understand what I am presenting here, you will have to read Chris’s article and download his workbook to follow this.

The M script in the workbook uses one cell input tables to store the beginning and end dates to size the date table as desired. It would be nice to size the date table based on the date range of a fact table in the PowerPivot Data Model. This would be a table that is refreshed periodically and the date range of the fact table would change as a result. Instead of manually updating the one cell input tables after visually determining the minimum and maximum values of the column in the fact…