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
Hi Murray,
Great to see you're giving Power Query a go.
Thank you for your detailed description and file. I've read through it several times but as the oil industry isn't something I'm familiar with it's a challenge to follow the exact details and although you've given a clear example, I'm unable to relate it back to data in the file to check my understanding.
That said, I wonder if you can simply bring in the daily load column to your CombinedQuery. Will this not give you the data you need to add back?
I'm thinking these calculations will need to be done in the query instead of the PivotTable.
If that's too simplified then this illustrates my lack of understanding 🙂 It would help to see an example your desired result using the data in the file you shared, so I can follow it from the source data through to the final query result you'd like to see.
Mynda
Hi Mynda,
Thanks for the response. That was my thinking as well to put the calculation into the CombinedQuery, but believe me my knowledge of Excel is far less than your knowledge of oil.
Is there a formula I could put into the CombinedQuery in a new column that would do the following:
By using the Tank as the base factor, calculate the difference in the tank from the previous day? I would then be able to take it from there.
Murray
Edit : I have updated this post to include a further explanation of the overall process of measuring the oil and what I am trying to achieve in my project.
Hi Murray,
Can you please upload your Excel file again. It looks like it has been removed when you did the edit.
Thanks,
Mynda
Attached are the two files, the spreadsheet and the additional pdf summary.
I am including a revised copy of my spreadsheet to include a new tab (DEMO) which gives the output I am attempting through Query and Pivot. This is the old method I was using which resulted in hours and hours of work to create these for each tank by month, then create consolidations etc.etc
If I can accomplish this with Pivot Tables, it will save me a tremendous amount of work as well as increase the accuracy of my reports.
A demo would be perfect, but I can't see it in the Production-Data-v1.0.xlsm file above.
I created a ver 1.1 as I am not sure if the attachments were getting mixed up with the uploading and editing. There should be a tab DEMO which demonstrates how I did this previously manually.
Hi Murray,
Thanks for uploading the file again. In the attached file you'll see the Prior On Hand BBLs and Daily Prod BOPD were calculated using Power Query (see Query sheet), and the Total Prod MTD was done with a PivotTable (see PivotTable sheet).
The Pivot sheet contains the solution that matches your 'Demo' and its starting point is your CombinedQuery, which doesn't include some of the data in your 'Demo' e.g. Feet and Inches. I trust you can make the necessary changes to bring this data in.
Note: It would have been quicker and easier if your demo used the same column names as the query and source data. I had to make some assumptions by matching the values in the columns to know which column was which.
I hope that points you in the right direction and gives you enough pointers to take it from here.
Mynda
Mynda,
Thank you for your prompt responses. I however do not see an attached file.
Mynda,
This is EXACTLY the solution I was looking for. I would never have been able to come up with the solution myself.
Thank you very much.
Murray
Great! Glad I could help.
