Excel data model without using PowerPivot

Question

I have a question about "Data Models" in excel. Whenever I read about this feature, 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 using the data model function (via the ID column), I thought that I could join tableB.info2 to tableAand have a table that showsID,info1,info2

But is this not possible, or is it possible, and am I doing something wrong?

Is there anything you can do with datamodels without using PowerPivot? I feel that I am missing this feature.

Answer 1

PowerPivot - , . .

" " "" PowerPivot, PowerView .

, , , VLOOKUP INDEX(MATCH()),

, , , , .

-,

1 2, , 1, 1.

1,

=RELATED(table2[info2])

, "related info2"

.

Excel? "" , VLOOKUP MATCH(INDEX())

, Excel, "Flattened PivotTable".

PowerPivot Editor, " ".

Flattened PivotTable Excel, 2, info2 , .

PivotTable , , "rows", "related info2" , 1.

, "" " ", "Grand Totals", .

, " " "", "".

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?)

Excel, , , "related info2" .

, , , VLOOKUP INDEX(MATCH())

Answer 2

Excel 2013, DataModel PowerPivot. ( ), DAX ( Calculated Columns). : https://youtu.be/FVVK-8QZC1M ( , DAX PowerPivot )

https://blog.crossjoin.co.uk/2012/07/16/what-are-the-big-changes-in-excel-2013-for-bi/ ( , , DataModel PowerPivot. PowerPivot - UI, - DataModel)

https://powerpivotpro.com/2014/07/adding-tables-to-a-model-from-vba-in-excel-2013/ ( , DataModel VBA)

http://dailydoseofexcel.com/archives/2017/07/10/look-ma-no-powerpivot/ ( , )