Create a dynamic table from powerpivot data in Excel 2013 without vba

Inspired by Chris Web's blog post about Dynamic DAX Query Tables in Excel 2013 - link - I have tried to create a dynamic table from PowerPivot data without using VBA so it would be possible to publish it on SharePoint and browse the model using Excel services.

So how could we do this - let's start by adding some data from the AdventureWorksDW to a data model in the workbook.