cmckayneal
New Member
- Joined
- Jul 20, 2020
- Messages
- 4
- Office Version
- 2016
- Platform
- 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?
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?
