Power Pivot, Measures

_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