Blank cells not working with Power Pivot?

Hi dear forum,

I am quite new to working with Power Pivot and have found it very beneficial. I have encountered one issue however:

I have added a new Data Model to my Excel File in which I have some blank cells. For this data model, I'm creating groups of Pivot Charts. I've created about 100 charts already pretty quickly and had no issues whatsoever because I used only "Count" in the "Values" section.

For one group of charts, the "Average" is needed. When it's selected, I get the error message:

"We can't summarize this field with Average because it's not a supported calculation for Text data types."

At first I was quite confused as I didn't know what was causing this. After playing around with the data a bit, I've found out that somehow Power Pivot is recognizing the blank cells I have in my Data range as a Text data type.

I should also note that the blank cells are not actually blanks, there are formulas pasted in as following:

=IF('Raw Data'!D3888="","",'Raw Data'!D3888)

Is there any workaround for this?

Please feel free to let me know if you need additional info.

Best regards,
Adam

Hey there,

Thanks for your reply!

it's already changed to Number, on the "empty" cells as well. I think that the Power Pivots have an issue with the formula that's in those "empty" cells. Cannot be sure though.

What do you think?

Cheers,
Adam

You mean the Data Type in the Power Pivot? Well, on there, it is set to Text. I've tried to change it to "Decimal Number" before but it returned the following error message:

============================
Error Message:
============================

The following system error occurred: Type mismatch.
Datatype conversion failed for [Table: 'Table1', Column: 'TTR', Value: ''].

I think Value: " are those empty cells, right?

Thanks,
Adam

No, to explain a bit further:

My file has one source - the Raw Data. That's unprocessed data extracted from a website. I then have the Processed Data sheet, where that raw data is either processed into more suitable formats or calculated to display a certain metric.

I have added the Processed Data into the Power Pivot data model as it's processed and ready to be displayed in form of Pivot Charts and Pivot Tables - precisely what I need.

The Processed Data tab is also the reason why I have so many empty cells. I have to create the function I mentioned above (=IF('Raw Data'!D3888="","",'Raw Data'!D3888)) because the Processed Data table doesn't expand automatically with new data entered into the Raw Data sheet.

I think if I would use Raw Data as the data model this issue wouldn't happen as I firstly wouldn't have any blank cells because the table would expand automatically upon entering new data and secondly because even if there were blanks, they would be really blank with no formula in the background as it's of course the case with the column in the Processed Data. I think that's precisely why Power Pivot has an issue with that.

Do you know any workaround?

Many thanks,
Adam

Npe, not possible either, same error message. Creating the chart with Raw Data works. I think I'll just create that group of Pivot Charts out of the Raw Data table. It will look ugly but I don't think there's another way

But thanks a million for your help with this one!

Did you ever get a solution to your issue as I am having the same issue.
If the formula returns a "", the data model table converts the column to text and won't allow me to change to a number.
I know if I place a 0 instead of "", this works but I do not want 0's as it throws off my averaging.

Are there any other numeric characters that can be used in place of "" that the data model table will like and any calculation will ignore? I have tried "-" but didn't work.

Craig

Originally Posted by CraigsWorld

Did you ever get a solution to your issue as I am having the same issue.
If the formula returns a "", the data model table converts the column to text and won't allow me to change to a number.
I know if I place a 0 instead of "", this works but I do not want 0's as it throws off my averaging.

Are there any other numeric characters that can be used in place of "" that the data model table will like and any calculation will ignore? I have tried "-" but didn't work.

Craig

Did you ask this on /r/excel ? I was in the middle of answering it there.