You have two quirks in NV Energy’s energy data download that need to be taken care of before doing your NV Energy Pivot Table. The things we need to tweak:
- The energy Usage column has to be adjusted to negative, when the “powerFlow” column equals “Received”, so we can compute Net Usage. So we need to create a derived "Real Usage column.
- We have to keep track of the sum of “Actual” Read Types, vs. “Calculated” per hour, so we know when a comparison with Sense is actually meaningful. To do this, we need a derived indicator column, “IsActual”, that is set to 1 if the read is “Actual” and 0 if it is “Calculated”.
- As before, we also need a text version of the DateTime hour for each measurement.
Once we have those 3 derived columns, it’s time to do a PivotTable with the following specifications:
Once you have that, result, you can copy and paste to merge with the filtered and processed (PivotTable) Sense data. You’ll only want to do detailed comparisons against Sense when you have 8 Actual reads in an hour.