New to Power Query and Pivot Tables, assistance needed.|Power Query|Excel Forum|My Online Training Hub

Hi All,

I am new to Power Query and Pivot Tables and trying to transition from a spreadsheet with thousands of formulas having to be entered to Pivot Tables and Dashboards which seem far more efficient.  Here is the issue I have:

We manage wells that produce oil and want to simplify the way we track production and ultimately put together a dashboard allowing us to track daily cumulative levels in our tanks, daily production of oil to each tank and then the combined production per lease.

Overview.

We have “Leases” which is the term used for the area of production which in turn contain various “Tanks” on these Leases which oil is produced into.  

We receive gauging from the oil tanks daily showing the levels in feet and inches which is then converted to barrels based on the “strapping” ratio.  By deducting the current days level from the previous days level we get the barrels of oil per day (bopd).

Once the tanks reach a certain level we call in a “load”, the gathering company comes out and removes the oil.  When they do this, they take a measurement before taking the oil in feet and inches and again after drawing the oil and then indicate how many barrels of oil was taken and enter this in the Load Ticket.

I have created two separate sheets in my workbook where I record the data from the daily gauges for the tanks and another for entering the load data using input forms I created. These are all converted to barrels and combined into a Query.  I am then able to create a Pivot Table from this Query and using the formula for difference from prior day I am able to get to the daily difference in the tank level. HOWEVER, I am unable to come up with a solution to add back the load taken to get me to bopd as it is currently off on the days a load is collected.

For example, if the bopd is 5 barrels one day, then 6 barrels the next, then 7 barrels the following and a load of 150 barrels is drawn on the day when bopd was 6 barrels, I will end up with a negative 146 bopd for that day which is incorrect.  I need to come up with a solution to add back the load for that day to show the correct 6 bopd.  I am able to do this in an excel spreadsheet where I would have a separate column that showed the daily bopd, then when a load was taken, I just added back the amount of oil that was taken that day and got the correct bopd, but I want to accomplish this with the new format I am creating.

Once I am able to get this calculation resolved I will then be able to continue with the other features where I will be able to create separate worksheets based on the lease names and build my dashboard.  I just need to find this solution before I can move on.

Any assistance will be greatly appreciated.

Thanks