Streamline Power Query for Excel

detriez

Board Regular
Joined
Sep 13, 2011
Messages
83
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

macfuller

Active Member
Joined
Apr 30, 2014
Messages
257
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
83
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
4,680
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
83
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
 

Forum statistics

Threads
1,089,578
Messages
5,409,104
Members
403,251
Latest member
BAMORAN

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top