I have some Powerpivot tables with calculated fields doing simple things like SUM... if my source table happens to be empty on refresh, I get an error message that SUM takes an argument of numbers and cannot evaluate a String. Is there something I can add to my formula to catch this? I tried wrapping it in IFERROR but it still doesn't help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Powerpivot formulas break when source is blank
- Thread starter cr731
- Start date
I've found that the issue is that, when the columns being summed are blank, the data type changes to Text, so the formula results in an error because it reads that as a string and SUM accepts numbers.
But even if I manually change it from Text to Number, it changes back to text the next time the cells become blank.
Is there a way to permanently set the Data Type and prevent Power Pivot from changing it?
But even if I manually change it from Text to Number, it changes back to text the next time the cells become blank.
Is there a way to permanently set the Data Type and prevent Power Pivot from changing it?
Upvote
0
PentaGalCXO
New Member
- Joined
- Jan 31, 2012
- Messages
- 46
Couple thoughts come to mind.
1) there are differences whether you refresh a PowerPivot table from the ribbon: Home/Refresh vs. Design/TableProperties/Save. Based on my experience Home/Refresh will not change data types on import. Design/TableProperties/Save will change data types. Do you experience your issue with both types of refresh???
2) You didn't mention your data source type. I'm guessing it is the Text Import wizard or similar, which will type cast on the fly if there is no schema.ini present. If this is the case, it can be remedied by simply creating a schema.ini file & editing it to specify the data type. The steps are detailed here.
1) there are differences whether you refresh a PowerPivot table from the ribbon: Home/Refresh vs. Design/TableProperties/Save. Based on my experience Home/Refresh will not change data types on import. Design/TableProperties/Save will change data types. Do you experience your issue with both types of refresh???
2) You didn't mention your data source type. I'm guessing it is the Text Import wizard or similar, which will type cast on the fly if there is no schema.ini present. If this is the case, it can be remedied by simply creating a schema.ini file & editing it to specify the data type. The steps are detailed here.
Upvote
0
