Equivalent of an HLOOKUP / INDEX(MATCH) across columns

[hendryj]

Hi there;

I havent come across a potential solution so keen for thoughts;

In the example below; the Customers B + C are moved in June to Salesperson 2 - so I want a way to allocate sales based on the month.... is something like this even possible; 

In excel I use the Index/Match to pull a column reference (basically the equiv of an HLOOKUP).

I am wondering if there is a measure (or some other method) that could be created to achieve this.

Customer List	May-17	Jun-17
Customer A	Sales Person 1	Sales Person 1
Customer B	Sales Person 1	Sales Person 1
Customer C	Sales Person 1	Sales Person 2
Customer D	Sales Person 1	Sales Person 2

Another option could potentially be where I link it to a 'date effective' column; like the below table

Customer List	Sales Person	Date effective
Customer A	1	May-17
Customer B	1	May-17
Customer C	1	May-17
Customer D	1	May-17
Customer C	2	Jun-17
Customer D	2	Jun-17

Then I would run a column based formula in my Sales Query which lookedup the related Sales Person based on the combination of the customer and month?

I hope that makes sense 

Any thoughts would be awesome; or even a link to another page which might solve this...?

Regards,

[jthomson]

Could you not just unpivot all the month columns you have in your first table, so you've got something that looks a bit like the second one but has a separate entry for each month even when the salesman doesn't change (so in your sample data you'd have the second table but extra rows saying A/1/June and B/1/June)?

[hendryj]

Hi jthomson, thanks for your reply.

I could do this but this would be a last resort as this means I need to update my databases every month, rather than just when things change. They do not change very frequently.

Not the end of the world, but would like to check to see if there are any other options... 🙂

[v-ljerr-msft]

Hi @hendryj,

---

I could do this but this would be a last resort as this means I need to update my databases every month, rather than just when things change. They do not change very frequently.

---

If I understand you correctly, you should be able to use the Upivot Other Columns option in Query Editor to unpivot the month columns in your scenario. In this way, you don't need to update your databases every month. For more details about how to Pivot and Unpivot with Power BI, you can refer to this article. 

Regards

[hendryj]

Thank you very much; I will try this out and see if I can get it to work 🙂