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 table containing the dates, the following technique could be used.

The first step is to create two measures to calculate the min and max values:

MinDateValue:=MIN(Fact[DateKey])

MaxDateValue:=MAX(Fact[DateKey])

Then, if you are not a MDX expert like Chris (and I am definitely not one), create a PowerPivot pivot table and add the two measures to the pivot table. Next, convert the pivot table to cube formulas by highlighting it and then select from the menu Pivottable Tools, Analyze, OLAP Tools, Convert To Formulas. If the upper left cell of your pivot table was cell A1, you will see the following CUBE formulas in A1 and A2 respectively:

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[MinDateValue]”)

=CUBEVALUE(“ThisWorkbookDataModel”,A$1)

The next step is to create a cube formula that is independent of cell references, so by replacing A$1 in the 2^nd formula with the first formula, we obtain:

=CUBEVALUE(“ThisWorkbookDataModel”,

CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[MinDateValue]”)

)

This formula will return the smallest date in the fact table. Repeating the steps for the max value gives this formula.

=CUBEVALUE(“ThisWorkbookDataModel”,CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[MaxDateValue]”))

Now, by copying these formulas into the one cell input tables (in the Date Parameters worksheet in Chris’s workbook), a dynamic updating effect for the Data Model is created. When the fact table is updated through PowerPivot with new rows and the Power Query M script is run, the generated data table date range will sync with the date range of the fact table. Once again, thanks to Chris for leading the way in showing the possibilities of Power Query.