• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / New to Power Query and Pivot Tables, assistance needed.|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryNew to Power Query and Pivot Tables…
sp_PrintTopic sp_TopicIcon
New to Power Query and Pivot Tables, assistance needed.
Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
1
December 11, 2018 - 4:19 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
December 11, 2018 - 10:36 pm
sp_Permalink sp_Print

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

Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
3
December 11, 2018 - 11:36 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
December 12, 2018 - 8:47 pm
sp_Permalink sp_Print

Hi Murray,

Can you please upload your Excel file again. It looks like it has been removed when you did the edit.

Thanks,

Mynda

Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
5
December 12, 2018 - 11:33 pm
sp_Permalink sp_Print

Attached are the two files, the spreadsheet and the additional pdf summary.

Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
6
December 13, 2018 - 5:18 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
December 13, 2018 - 9:41 am
sp_Permalink sp_Print

A demo would be perfect, but I can't see it in the Production-Data-v1.0.xlsm file above.

Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
8
December 13, 2018 - 10:22 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
December 13, 2018 - 12:34 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
10
December 13, 2018 - 12:58 pm
sp_Permalink sp_Print

Mynda,

Thank you for your prompt responses.  I however do not see an attached file.

Avatar
Murray C
Member
Members
Level 0
Forum Posts: 7
Member Since:
December 11, 2018
sp_UserOfflineSmall Offline
11
December 13, 2018 - 1:32 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
12
December 13, 2018 - 2:12 pm
sp_Permalink sp_Print

Great! Glad I could help.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Anders Sehlstedt, RAQUEL ACION, Chandler Davis, gogy13, Brenda Richards, Mark Luke
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 202
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
Forum Stats:
Groups: 3
Forums: 24
Topics: 6218
Posts: 27267

 

Member Stats:
Guest Posters: 49
Members: 31901
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.