Using Power Query to automate part of process with manual step in the middle

cmckayneal

New Member
Joined
Jul 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I am trying to use power query to automate a process that we go through prior to analyzing. The catch is that in the middle of the process there is a step that has to be done manually because it is not 100% rules based.

To simply what I am doing, I am preparing YOY sales data by customer, product, and sales rep for a growth analysis. I also pull in location hierarchy data from other tables (store, region, etc), as well as additional customer data (combined customers--see below). The first part of the process is add in the hierarchical data.

Part of the process involves cleansing the customers. Much of the customer data is manually entered by sales reps and customers can be entered multiple times with slightly different names (John Smith, J Smith, John Smith JR, Jon Smith, etc), which also means multiple customer numbers. I have to combine these customers, but this process is manual and objective, not rules based. Everything thing before this step and everything after is rules based. Once I manually determine customer combines, I add it to a running "Revised Customer" file. Then, I continue through more rules based steps to determine if a customer has decreased number of products purchased YOY, etc.

Here is the catch, the revised customer file is used at the beginning of the process to determine combined customers from the previous month, so that we don't have to manually review them again. Then when we review this month's and add to the file, it will get used at the beginning of the process for next month.

I want to set up a file using power query that can be used every month by loading the newest sales file only. I want to keep it simple because although I will be building the process, I will likely not be running it each month. Is this possible?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

cmckayneal

New Member
Joined
Jul 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
**Essentially, I want to pull out a table from a query, manually update that table, and have it then update another query. However, when the previous steps overwrite my manual updates once I close & load. We currently use multiple access queries for each step. Then excel for the manual steps. Then load the new file in to access, etc. We are trying to move away from Access and into Power Query in order to integrate with Power BI later.
 

cmckayneal

New Member
Joined
Jul 20, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,112,817
Messages
5,542,670
Members
410,567
Latest member
SCraig123
Top