Power Pivot, Measures

[_Parjam_]

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?  

[#2]

Hi
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?

[#3]

@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. 

[#4]

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

[#5]

@Wyn Hopkins 

I will try to give you the whole picture. When I load from power query directly to the data model, the spreadsheet hangs on refresh, I tried to solve this with tech support but it could not be resolved, I think it has something to do with the structure of the spreadsheet. 

The project I am working on requires a table that keeps all the data but at the same time it requires that those sheets where the data is input can be erased and re used.

If you look at the sample sheet I included in this post, you will se that the table P_L in TOTALS is like a historical table which receives its data from tables Sales1 and Sales2. Table P_L is loaded to the TOTALS sheet from P&L in power query and it loads back to power query P&L through P&L APPEND. This way Sales1 and Sales2 serves as some sort of input table and P_L stores all the data even if its erased from Sales1 and Sales2. 

This resolves the "Historical data" issue, however when I load P&L from power query to the data model refresh hangs. So instead I have to load P_L from the sheet to the data model, this works fine until I clear all the input data on the spreadsheet, including the Historical data from P_L, when all the data is erased all the pivot tables and pivot graphs collapse, the measures produce error calculations and the spreadsheet looses its structure. 

If you erase the data on the tables SALES1, SALES2, P_L and refresh the spreadsheet you will se what I am referring to in the PIVOT_TBL_GR sheet. 

I used Calculate in the measures so that it would reproduce the same error in the sample sheet as original sheet as the formula requires Calculate in the original. 

Any help on how to avoid the Power pivot tables and graphs to loose its structure when no data, or on how to make a historical table which serves the above mentioned purpose and can be loaded directly to the data model, would be highly appreciated.

[#6 · Accepted solution]

@_Parjam_ 

You can't add data directly to data model, thus yes, you need to keep P&L in Totals. When you clean all data data model can't recognize which data types are loaded and by default assumes everything as Text. Thus measure returns an error. Thus PivotTable becomes destroyed and lost the structure.

I'd suggest

1) Keep dummy table as

with any date after Feb 1900, product name which definitely won't be repeated in actual data, any other numbers.

2) Delete P_L Append query and do everything in in one P_L query, in particular

et
    Source = Table.NestedJoin(
        SALES, {"PRODUCT"},
        COST_TBL, {"PRODUCT"},
        "COST_TBL", JoinKind.LeftOuter),
    PLappend = Excel.CurrentWorkbook(){[Name="P_L"]}[Content],
    PLappendFiltered = Table.SelectRows(
        PLappend,
        each ([#"DATE "] <> null)),
    dummy = Excel.CurrentWorkbook(){[Name="dummy"]}[Content],
...

combine all together, set data types and filter nulls

...
    #"Appended Query" = Table.Combine(
        {#"Added Custom1",
         PLappendFiltered,
         dummy}),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Appended Query",
        {
            {"DATE ", type date},
            {"PRODUCT", type text},
            {"UNITS SOLD", Int64.Type},
            {"PRICE", Currency.Type},
            {"COST", Currency.Type},
            {"P&L/UNIT", Currency.Type},
            {"P&L", Currency.Type}}),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type",
        each ([#"DATE "] <> null)),
...

 3) modify measures as

COSTS:=CALCULATE(SUM(P_L[UNITS SOLD]), P_L[PRODUCT] <> "no product" )
SOLD:=CALCULATE(SUM(P_L[PRICE]), P_L[PRODUCT] <> "no product" )
TOTAL UNITS:=CALCULATE(SUM(P_L[UNITS SOLD]), P_L[PRODUCT] <> "no product" )

4) disable background refresh for queries to be sure in proper PivotTable refresh

With that if you delete all data and refresh PivotTable/Chart will be empty but correctly structured

If add any sales data and refresh

[#7]

@Sergei Baklan 

Thank you for that solution and specially for the M Code. Works very well. 

[#8]

@_Parjam_ , you are welcome