Streamline Power Query for Excel

detriez

Board Regular
Joined
Sep 13, 2011
Messages
78
I am just discovering the power or Power Query for Excel

I need to run merge queries to populate data on my daily file from the same master file every day and am trying to streamline the process.
My current process
  1. Open my daily file
  2. Get & Transform From Table/Range
  3. In power query editor.. New Source > File > Excel
  4. Open Master file
  5. Merge queries
  6. select map columns
  7. merge
  8. Add columns to daily file
  9. Close & Load
  10. Delete original data tab form daily file
  11. Delete master data tab from daily file

Can I just reference the table on my master file without importing it and then deleting it?
Are there any improvements I can make to streamline my process?
 

macfuller

Board Regular
Joined
Apr 30, 2014
Messages
242
Are you asking if you can keep the previous data in the data model and just import the new daily file? If so, the answer is no. Every time you execute the query it's all or nothing - all data including anything new has to be run every time.
 

detriez

Board Regular
Joined
Sep 13, 2011
Messages
78
thanks for the reply.

Is it possible to link to the master sheet rather that import it in order to merge?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,230
I'm not sure what are you trying to achieve but maybe From Folder option would be the solution
 

detriez

Board Regular
Joined
Sep 13, 2011
Messages
78
I've found it!

Sorry I wasn't more clear

From the power query editor, rather than Close & Load... I needed to select Close & Load to.. Then only create a connection
This way, I'm not importing the reference data into my file, I am connecting to it in order to facilitate the merge
 

Forum statistics

Threads
1,077,856
Messages
5,336,800
Members
399,103
Latest member
PX04

Some videos you may like

This Week's Hot Topics

Top