Excel 2016 Data Model: Possible to view Data-Model data as "normal" table?

Question

[Microsoft Office 365 ProPlus - Microsoft Excel 2016 MSO (16.0.7329.1037) 32-bit]

Please note: A solution that has no external dependencies in any way is required.

Excel Data-Model and plain tables?

Is it possible to display simple non-grouped table data from an existing Data-Model in the same Workbook?

Details

This is a much simplified description of the complete functionality in this Workbook, and this info is just to give some context to this question. So please consider the setup to be hard customer requirements based fundamental religious, political and moral beliefs  :)

There is a single Workbook consisting of...

• a Data-Model consisting a single Customer table with these columns...
  • Id (int)
  • Name (string)
  • Email (string)
  • ZipCode (int)
• Two work sheets
  • Sheet 1: All Customers
  • Sheet 2: Customers in Zip

In the sheet named "All Customers" there should be a "normal" Excel table which displays Name, Email and ZipCode of each customer. The normal filter and sort features should be available on this table. The table should be read only (or at least, no changes in the table should be reflected back to the data-model).

In the sheet named "Customers in Zip" there should be a "normal" Excel table which displays Id, Name and Email.

Above the table there should be a ComboBox with a unique list of ZipCodes based on available ZipCodes on customers in the Data-Model. Changing the selected ZipCode in this ComboBox should trigger a reload of the table in this sheet and apply the newly selected ZipCode as a filter, so only customers in the selected ZipCode are shown in the table.

Normal table based sorting and filtering should not be possible on this table.

The table should be read only (or at least, no changes in the table should be reflected back to the data-model).

Questions

1. Is it possible to insert a plain table based on the Data-Model in the current Workbook?
2. Is it possible to insert a plain table based on a query/filter of data in the Data-Model in the current Workbook?

Alternative Solutions

If this is not possible, what is then the best solution to have common shared data in a workbook that are displayed in various ways and in various part of the workbook?

Answer 1

Hi,

This should be possible to do using CUBE formulas.  Upload your data (sample would be fine) to OneDrive and share the download link here.  Please also show your expected result there.

Answer 2

Hi 18876955438,

It is not feasible to insert a plain table when you report the Data-Model.

As a workaround, I suggest you try the following steps:

1. Report the Data-Model.
2. Copy the table which reported the Data-Model.
3. Paste to the sheet.

If you want to use the feature, we welcome and encourage you to share your ideas through Excel UserVoice. Understanding your experience helps us to make our product and service better for you and others.

Thanks for your understanding.

Qing