Well if you still want the values to be shown as “Null” and not the zero’s (0) then just add one more step in the file which I’ve provided. Initially, I’ve transformed the null values to 0 and then unpivoted the data. Now, after unpivoting the data again transform/replace the zero’s (0) with the null values you shall have the desired result. Below is the screenshots provided for the reference -
Below is the screenshot provided of the overall steps performed till now -
Still the file will remain refreshable. If I update the data in excel file and refresh it in the QueryEditor it incorporates the changes without breaking your query or showing any signs off error. Below are the screenshots provided for the reference -
And then in Power BI you can have the analysis as shown below in the screenshot -
For solution by using a Formula. Below is the formula provided for the reference -
Total Values =
CALCULATE( SUM( 'Required table'[Values] ) ,
FILTER( 'Required table' ,
'Required table'[Values] > 0 ||
'Required table'[Values] <= 0 ) )
I’m also attaching the Excel as well as the PBIX file for the reference.
Hoping this meets your requirements.
Thanks and Warm Regards,
PQforNoDataMonth.xlsx (21.2 KB)
Power Query.pbix (20.9 KB)