THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Linked Table, Reverse Linked Table and Linkback Table in Excel 2013 #powerpivot

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.

Published Monday, January 21, 2013 3:35 PM by Marco Russo (SQLBI)
Filed under: ,



Bart Davidson said:

Macro, is it possible to achieve linked back tables in excel 2010 or is 2013 a necessity?


January 13, 2014 4:52 PM

Marco Russo (SQLBI) said:

With 2010 you cannot obtain the same integration, with automatic refresh of linkback table, unfortunately.

January 13, 2014 11:47 PM

Maria Heygate said:

Interesting stuff. I'm becoming addicted. Have created a Data Model with several calculated fields (yes!) and a great Pivot Table. When clicking within the pivot table, a sheet opens with the rows pertinent to that portion of the pivot. Perfect.  My question is:  How do I format the number columns in that 'sheet', which will change each time I click in different pivot sections/lines? To my surprise, though my data model has all columns with numbers showing as numbers (formatted as Decimal Number), the automatic 'sheets' show the columns with numbers FORMATTED AS TEXT.  Which is bad if one needs to filter, use for calculations, etc.  Where are the settings to change that formatting so that ANY SHEETS that I open will reflect portions of the pivot, with numbers FORMATTED as NUMBERS?  Thank you so much!

April 8, 2015 8:58 PM

Maria Heygate said:

Hi Marco, forgot to specify that I do know that the function =Value() will convert text to number. However, that would be a manual calculation needed EVERY TIME I open a specific sheet from the pivot table. What I need is to change the settings SOMEWHERE so that the sheets that open up will always show numbers as numbers, and not as text. Thank you, I do appreciate it!

April 8, 2015 9:11 PM

Marco Russo (SQLBI) said:

Maria, unfortunately the drillthrough operation does not format data correctly in Excel. It works for DAX queries (e.g. Reverse Linked Table as we explained in the article), but not for the drillthrough.

April 11, 2015 12:42 PM

Maria Heygate said:

Thank you, Marco. Maybe they will fix that in a future release :).  Can you (or anybody else in the community) recommend a couple of articles or a video with steps for Linkedback and Reverse Linked tables? I can't always get to the TABLE TAB that is essential to link back, I have looked and looked, so I must have the wrong path, and I need to get good at it. Eternally grateful.

April 13, 2015 8:37 AM

Marco Russo (SQLBI) said:

April 13, 2015 8:40 AM

Maria Heygate said:

Yes, I have. The author says:  "You select the Connections button in the Data Ribbon and in the Existing Connections dialog box you select the Tables tab, choosing the table you want from the data model."   That is pprecisely the TABLE TAB that I cannot find!!! In my Data Model, my Existing COnnections Dialogue Box does NOT have 2 Tabs for me to choose the Table Tab. No tabs. It only has one Dialog Box: "Select an Existing Connection", with Browse, Open, Edit,Refresh or Delete at the bottom  So sorry to be such a pain, I stop dead there, as it is a crucial step to continue and it fails to appear. Maria.

April 13, 2015 12:57 PM

Marco Russo (SQLBI) said:

Are you using Excel 2013 or Excel 2010? THis is supported only in Excel 2013.

April 13, 2015 1:03 PM

Maria Heygate said:

WOW! Found it! I was looking at Existing Connections in the Data Model. But what tha author means is I had to go back to excel, DATA RIBBON, then there is another Existing Connections dialog box in there. And in there is my TABLE tab!  I love it! One can never ever ever give up!!!!


April 13, 2015 1:19 PM

Marco Russo (SQLBI) said:

Nice - thanks for the feeback!

April 13, 2015 1:30 PM

Dmitry said:

I have a problem with model which was created by PowerQuery.

I cannot crate linked table cuz that option is grayed.

On the other hand DaxStudio is able to do that. Is it possible somehow to do it in directly in Excel?  

May 12, 2015 7:45 AM

Marco Russo (SQLBI) said:

May 26, 2015 5:37 AM
New Comments to this post are disabled

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.

This Blog



Privacy Statement