Create calculated tables in Excel 2016 Data Model

I have multiple tables in Excel 2016 data model. These tables come from data maintained in other excel worksheets and are imported through Excel Query to populate a data model to take advantage of superior data management features that are available (e.g., DAX, date tables, relational joins, etc.)

However, I would like to be able to create "calculated tables" (with DAX expressions) by applying filters, unions, etc. to target and transform the existing data elements. The goal to use the "calculated tables" in the Data Model for pivot tables, etc. Is this possible within Excel 2016? If not, what complementary tools (apart from SQL) are necessary? TIA.

Solution 1:

As of now (August 2019), Excel does not support generation of calculated tables using DAX. I personally believe Microsoft should consider this, given that DAX is efficient in data aggregation and analysis.

Get and Transform

Until having the feature implemented by Microsoft, there is a good alternative within Excel (versions from 2010 onward) by utilizing Get and Transform (called also PowerQuery in older versions).

Using this tool, you will be able to load the same data set (table or other data source) and transform it as a separate table into the data model.

Advantages:

• It is powerful in terms of source data conversion, which extends beyond simple filtering and can deal with importing raw data extracts.

I personally used this approach, and has saved me tremendous time.

Disadvantage:

• Get and Transform is a slower and indirect solution compared to DAX.
• You need to refresh the query sources, whenever there is change of the source data.

Where is it located

• In Excel 2016, to Get and Transform Tool is available Data/New Query.

Please let me know if you need any further explanation to my answer.

Solution 2:

I don't think so.

Here (Though i note the article is old):

Unfortunately, calculated tables are not available in Excel 2016. If you need a similar solution with Excel 2016, you can rely on linked back tables (i.e. queries over the data model materialized in Excel tables and then loaded back in the model). The only limitation is that the size of linked back tables cannot exceed the physical limit of 1M rows of Excel, whereas DAX calculated tables have no limit in size and can work in many more scenarios, resulting in a very elegant and neat model.

I think it is for Power BI particularly using the "New Table" feature and SSAS tabular using new calculated table:

Uses for the New Table Feature in Power BI

This is only available in Power BI Desktop and not in any of the Excel versions or SSAS Tabular. This feature is essentially a “Calculated Table” function. You can pass any valid DAX measure that returns a table of values, and the table will be materialised and loaded into the data model.

And with SSAS calculated table.

How to create a calculated table

First, verify the tabular model has a compatibility level of 1200 or higher. You can check the Compatibility Level property on the model in SSDT.

Switch to the Data View. You can't create a calculated table in Diagram View.

Select Table > New calculated table.

Type or paste a DAX expression (see below for some ideas).

Name the table.

Create relationships to other tables in the model. See Create a Relationship Between Two Tables (SSAS Tabular) if you need help with this step.

Reference the table in calculations or expressions in your model or use Analyze in Excel for ad hoc data exploration.