Forum Discussion
Power Pivot, Measures
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
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.
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
Thank you for that solution and specially for the M Code. Works very well.
_Parjam_ , you are welcome