Search code examples
mysqlexcelodbcdatamodel

Excel Data Model without using PowerPivot

I have a question about the "Data Model" in excel. Whenever I read about this function, it is used with PowerPivot.

I ask because I would like to do something like this:

I have table A:

  • ID
  • info1

And table B:

  • ID
  • info2

Now if I connect these tables with with the data model function (through the ID-Column), I thought that I could then join tableB.info2 to tableA and have a table that shows ID,info1,info2

But that doesn't seem to be possible, or is it possible and I'm doing something wrong?

Is there ANYTHING you can do with datamodels without using PowerPivot? I feel like I'm missing the point of this feature.

Solution

  • PowerPivot is a highly advanced tool that takes the hassle out of tables. It saves you a whole lot of time.

    The "Data Model" is at the "core" of PowerPivot, PivotTables, and PowerView, and cannot be used apart from either of these three tools.

    What you're talking about can be achieved, but is best handled in a VLOOKUP or INDEX(MATCH()),

    But let's just say we want to go down that route, because honestly I do sometimes.

    First,

    Hook up your ID values in your data model between table 1 and table 2, the arrow flowing towards table 1 that only has info1.

    Create a calculated column in table1, give it the formula

    =RELATED(table2[info2])

    Name that new column you just added "related info2"

    And your value populates magically.

    But how do you get it back into your excel workbook? It's not at all a "clean" solution, which is why we recommend VLOOKUP or MATCH(INDEX())

    What you'll want to do to bring your info2 into your excel sheet is use a "Flattened PivotTable".

    You can create this type of PivotTable in your PowerPivot Editor, by pressing the "PivotTable" button.

    Put your Flattened PivotTable right beside the other table in your Excel worksheet that you want info2 at, like you're magically going to populate the info2 column right beside it, because you're about to.

    Drag and drop all PivotTable fields from the table that contains our calculated column to the "rows" box, including that "related info2" column we just created in table1.

    To get the right values on the right rows, go into your Design tab, and click on the "Subtotals" button, disable all subtotal, and do the same with the "Grand Totals" button, disable all grand totals.

    Make sure your "Report Layout" is "Compact", also found in the Design tab.

    Optional: You can add a field to "values" to allow row formatting to recognize the rows, otherwise the PivotTable thinks that there are no rows and you won't get anything but a mostly solid color, no matter how many designs you try to switch to (we're doing something tricky, remember?)

    Now hide all the columns in your excel sheet you just created with the PivotTable except your "related info2" column.

    So yes, it is possible to do, but you're better off with a VLOOKUP or INDEX(MATCH())