Power Pivot, Measures

Occasional Contributor

Hello, I have a spreadsheet, which includes measures, pivot tables and pivot charts. I would like to be able to save the spreadsheet without any source data. How ever, when I erase the source data the pivot tables and the pivot charts disappear from the spreadsheet, as the measures produce errors due to the data format change which power pivot does from whole number to text value when a source table in not populated. Is there any way to avoid power pivot from changing the data type or any DAX solution to this issue?  

3 Replies
Are you using Power Query to load the data into Power Pivot?
When you say you erase the source data what steps are you actually doing? Where is the source data held and how is it getting loaded into Power Pivot?

@Wyn Hopkins Unfortunately I can not share the spreadsheet here, but I have recreated the same problem in the attached spreadsheets. The main question is whether it is possible to maintain pivot tables and pivot charts intact with all the fields and headers, when the source data in the tables are erased. It seems that the fields disappear due to the error in the measure formulas, the error could be avoided if the data model would not change the data type to text when columns have no data. 

I don't understand sorry, if you delete the data nothing will happen, unless you click refresh.
However when I click refresh it fails as there's an odd relationship with the Calendar.

Side tip, you don't need CALCULATE in your measures