Streamline Power Query for Excel

detriez

Board Regular
Joined
Sep 13, 2011
Messages
87
Office Version
365
Platform
Windows
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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

macfuller

Active Member
Joined
Apr 30, 2014
Messages
270
Office Version
365
Platform
Windows
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
87
Office Version
365
Platform
Windows
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
5,451
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
87
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,958
Messages
5,471,708
Members
406,779
Latest member
a_faulding

This Week's Hot Topics

Top