Data Model Restrictions

[jaustin]

Running Excel 365 Pro Plus. Have a table with over 100,000 rows and have established several Pivot Tables using this table (not related to my data model). One of the fields is a currency field and have been able to put this variable in the Values pane as count, sum, or average Value Field settings without issue. I've recently created a data model that has this same table along with other related tables. When I try and use the same field (as average) and as successfully used without the data model relationship, I get an error message "We can't summarize this field with Average because it's not a supported calculation for text data types". I've check the field and there are no text data entries. I've checked the other tables in the data model and this field is not utilized. I have a very similar field in the table that does not exhibit this behavior. I've created very small test Pivot Tables and can duplicate the error. Both Pivot Tables are using the same basic table (one within the overall Data Model and one without). The base table has been refreshed so it should be the exact table for each PT application. It appears there is some unknown restriction when using the data model.

Thanks in advance for any ideas on this issue.

jarummel

 

[mole999]

365 has limitations, especially when cloud hosted > https://www.mrexcel.com/forum/lounge-v-2-0/1042955-office-365-a.html. whether that is what you are facing I don't know

 

[jaustin]

365 has limitations, especially when cloud hosted > https://www.mrexcel.com/forum/lounge-v-2-0/1042955-office-365-a.html. whether that is what you are facing I don't know

mole999,

Thanks for the link. It speaks mainly to potential limitations regarding VBA. I have both Office 365 and Office 365 Pro Plus (both cloud based subscriptions) and have not experienced any obvious VBA limitations in over 25 modules of code that utilize a large number of Excel features. I'm going to try and systematically go into the data model utilization and see if I can isolate the issue. It seemed to be working very well until I added another table.

Thanks again,

jarummel

 

[RoryA]

What is the field type shown as for that column in the data model?

 

[jaustin]

What is the field type shown as for that column in the data model?

RoryA,

In the test Pivot Table it is showing up as "General", while in the master table it is "Currency". Just changed it from General to Currency in the test Pivot Table but still got the same error.

jaustin

 

[RoryA]

When you say master table, are you looking at the table in the data model, or a table on a sheet? Also, are there any errors in the column?

 

[jaustin]

When you say master table, are you looking at the table in the data model, or a table on a sheet? Also, are there any errors in the column?

RoryA,

Your question brings up a question I'm beginning to have. I have a table named "Combined". It has approximately 60 columns and 100,000 rows. I have used this table successfully as the basis for over 6 Pivot Tables. I then added several other tables of data to my project and needed to have data analysis that combined information on the separate tables. I then ran into the Data Model and decided to utilize that approach (it seems to use the same relational concepts that I've used in Access). It did exactly what I needed and the fields from the data model appeared on the Pivot Tables for use. I did not change the Pivot Tables that were originally created against "Combined" and that continues to work as required. I then added a couple more tables to the Data Model (including "Combined" and have used a single data field (column) to connect/relate the tables. It was at that point I ran into the inability to do an average on one of the fields. There have been no errors.

I am now in the process of taking the data model apart one table at a time to see if the error goes away. My question that has arisen is that can a table be used separately as well as part of the data model without conflicts?

Thanks in advance,

jaustin

 

[jaustin]

RoryA,

I did some more digging, and when looking at the data in the data model page, the column is text. The table ("Combined") from which the data model table was formed is currency. Not sure when or how this happened. So I guess the solution is to delete the table from the data model and re-insert it and see what happens.

Will let you know what I find.

Thanks,

jaustin

 

[jaustin]

RoryA,

I did some more digging, and when looking at the data in the data model page, the column is text. The table ("Combined") from which the data model table was formed is currency. Not sure when or how this happened. So I guess the solution is to delete the table from the data model and re-insert it and see what happens.

Will let you know what I find.

Thanks,

jaustin

RoryA,

I tried several things that didn't work but finally was able to get around the issue. By a hard copy/paste of the large table into the data model as desired. The column being improperly imported by the data model as a text field rather than currency was OK once it was in the data model. To me, this means there is something (bug?) in the data model import process that misread that column. It did have some blanks but I went through each one and did a "selection.clear" which should have removed any issues within that columns data.

Also, even though I had deleted the table/relationships in the data model diagram window (and it was no longer displayed), when I created a new Pivot Table from the data model, it still showed as an option (as well as the new instance of the "Combined" table. To me this means it is still taking up a lot of space/memory. Is there any way to clean/repair the file that might clean up this anomaly?

Thanks for your continued interest and help in this issue. I find MrExcel an excellent source for the Excel community. Many of my searches for help on a particular issue bring up a MrExcel posting.

jaustin