• 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

Updating Files Using Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Updating Files Using Power Query|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 QueryUpdating Files Using Power Query
sp_PrintTopic sp_TopicIcon
Updating Files Using Power Query
Avatar
Richard Davenport

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 22, 2021
sp_UserOfflineSmall Offline
1
October 22, 2021 - 7:30 am
sp_Permalink sp_Print

I have a production board that is currently done by hand using magnets and want to automate using 2 files.

One file (SAPDATAFILE) will have data coming from SAP - 4 fields that would show updates of order.

Second file (ORIGINAL DATA FILE) would show where the job is in production using Green or Red cells (currently use magnets).  The cell info would be keyed by the workers on floor and changed based on conditional formatting hopefully.

The intended process -

Once the SAPDATAFILE is downloaded to excel file to update the orders the ORIGINAL DATA FILE is merged into the SAPDATAFILE and would be the NEW Current Board.

I have run Power Query and placed the merged file into the ORIGINAL DATA FILE as a worksheet (NEW BOARD).  This new board worksheet should be the one updated with Green or Red in cells with the next daily SAPDATAFILE.  

Questions I am having trouble with are:

CONFUSED ON:  If the query is updating each day using the Original File (INITIAL BOARD worksheet in the Merge process), how can I get the New Board worksheet data into the ORIGINAL DATA FILE (INITIAL BOARD worksheet) to be used for daily updates.  The New Board worksheet would be used by both Power Query and by workers to change cells Green or Red.

I plan on doing nightly automatic updates so the board is refreshed each morning or perhaps sooner if workers refresh the NEW BOARD during the day.  Do you see any problems with refreshes.  In particular, losing the Conditional Formatting of Green/Red cells.

Thanks for any help you can provide.

 

Richard

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
October 22, 2021 - 7:23 pm
sp_Permalink sp_Print

Hi Richard,

Welcome to our forum! Thanks for sharing your files.

If your workers make changes to the query output on the 'New Board' sheet, those changes will be lost when you refresh the query. Therefore, the solution is to have the workers only enter their information into the Initial Board table. You can then get the initial board and SAPDATA and bring them together into your New Board table to be used as a reporting tool only.

Hope that helps.

Mynda

Avatar
Richard Davenport

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 22, 2021
sp_UserOfflineSmall Offline
3
October 22, 2021 - 9:38 pm
sp_Permalink sp_Print

Hi Mynda,

 

Thanks for the quick reply.

The issue I can't figure out is the INITIAL BOARD will not be current as they are looking at it once the first merge is done.  I need to automatically get the NEW BOARD info into the INITIAL BOARD each time the merge is run.  How can I do that within Power Query or would it have to be some other means.

 

Thanks.

 

Richard

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
October 23, 2021 - 11:28 am
sp_Permalink sp_Print

Hi Richard,

You can't have the workers enter data into the resultant query table. Power Query can only display data in a table that it gets from somewhere else. It can't reference itself. i.e It gets data from Table A and puts it in Table B. When you refresh the query it will overwrite anything you type in Table B.

You would have to manually, or with VBA, create a copy of the data in Table B and paste it to Table C where your workers can enter their data, then reference Table C in your next query along with the SAPDATA, thus creating a copy of the query output for your workers to enter data in and also a new query to reference this new table each time you want to refresh the report.

I think you need a database that can take data entry, like Access, where your workers can enter their notes and you can import new data from SAP to keep the database up to date.

Hope that clarifies the limitations.

Mynda

Avatar
Richard Davenport

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
October 22, 2021
sp_UserOfflineSmall Offline
5
October 25, 2021 - 10:17 pm
sp_Permalink sp_Print

Hi Mynda,

 

Thanks for the help.  I figured I might have to use VBA which I am unfamiliar with.  I was able to get the file refreshed anytime a change was made but now have to get the copy/paste to work so query can reference the same file.  I prefer using Excel and not getting into Access.  I know Power BI but getting a new excel file for workers to use off BI would be too difficult perhaps.  I will do more research for Excel and VBA.

Thanks again.

Richard 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
October 26, 2021 - 9:36 am
sp_Permalink sp_Print

Hi Richard,

Power BI doesn't allow you to input data and it uses the same Power Query tool as Excel, so this won't solve the problem, unfortunately. If you get stuck with the VBA, you can post in our VBA forum.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: s Ramchandran, Shanna Henseler, Stacy Culpepper
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
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27237

 

Member Stats:
Guest Posters: 49
Members: 31889
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.