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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,604
Messages
5,838,326
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top