Create calculated tables in Excel 2016 Data Model

Question

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.

Answer 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.

Answer 2

Yes, it is possible to add DAX tables to data model in Excel.

• Use Existing Connection to get whatever table to Excel sheet.

Right click on a table and select Edit DAX. Then shape your DAX code after EVALUATE command.

Add this new DAX shaped table from Excel sheet to your data model.