Update query - power query

AliBb

New Member
Joined
Apr 28, 2018
Messages
25
I have a table of customers with an allocated sales lead - I have a table of transactions for customers. I've the relationship set up in power pivot and the salesman flows through no problem.

The issue I have is when the allocated sales lead changes.

The pivots give amounts by salesman for each period / year. With the query always dynamically looking up the sales lead the historic data is always skewed by any changes to the salesforce as customers get allocated when salesmen leave.

At the point of running the power query I would like the data to be updated permanently for the transactions that don't yet have a salesman identified on them - is this possible?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I'm not quite clear on your question as it appears your first question deals with how to handle historical data when the sales lead changes, but then you ask for data to be updated when there isn't a sales lead.

What is it that you'd like updated? If Bob had the Acme account until April when Alice took it over, do you want Bob to continue to get credit for historical Acme even though he now handles Spacely Sprockets or do you want Alice to get the historical credit?
For the pending transactions of Cogswell Cogs if they move to Ted when he gets the assignment will they "permanently" be Ted's, or will Carol get them sometime in the future if Ted gets assigned elsewhere?

Offhand it sounds like you need an "effective date" lookup table for when a sales rep owned an account.
 
Upvote 0
I would like the historical data to remain as is ie in the example you gave Bob would always have the credit for Acme and Alice would only have the credit when she took over.
The effective date lookup sounds like it may work. How would this be achieved using power query?
 
Upvote 0
The effective date data would have to be maintained somewhere it could subsequently be imported into the data model. Off the top of my head I'd say it's more of a DAX issue - you would want your total sales etc. measures to depend on the lookup of the effective date.

Without knowing how your data looks I can't be too specific. If you have a begin and end date for the dates that Bob had Acme that's different than if you have a single effective date for Bob and would have to check the account info for an effective date later than Bob's (e.g. Alice) to know the date range.

A quick Google search brings up this somewhat similar post on matching discount rates to customers for a certain time period. Or this one.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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
Back
Top