SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus
Kasper de Jonge wrote a blog post last year introducing an interesting new feature in Excel 2013: you can write a DAX query that extracts data from the PowerPivot model and returns a table in Excel. Such a table can be used as a Linked table for the same PowerPivot model, creating an interesting opportunity to inject data in a data model mixing existing data and calculations obtained with Excel formulas.
I wrote an article on SQLBI that describes this feature in more detail and we discuss this capability also in the book Excel 2013 Building Data Models with PowerPivot that will be available in March 2013 (you can already order it). Because data can flow back and forth between Excel tables and the PowerPivot data model, we created a particular definition for each type of “connected” table:
- Linked Table (Excel –> PowerPivot): the classical Linked Table
- Reverse Linked Table (PowerPivot –> Excel): this is an Excel Table contained data resulting from a DAX query to the data model.
- Linkback Table (PowerPivot –> Excel –> PowerPivot): this is a Linked Table based on a Reverse Linked Table (usually integrating some new columns filled with constants and/or Excel formulas).
The article shows how to create a Linkback table step-by-step, applying different Excel calculations to different rows (something you cannot do on calculated columns in DAX), and it also contains some consideration about refresh order that is applied to Linkback tables.
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Marco Russo (SQLBI)
Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.