VLOOKUPVALUE - find first of many

[Anthonsen]

Hi

I have a table (table 1) in my powerpivot model, that contains customerID and where I calculate a column that add either A og B. Multiple rows have same customerID but each specefic customerID have always either A or B. .

Now I also have another table (table 2) that contains multiple rows with same customerID. In this table I would like to add the either A or B from table 1.

I have tried to use LOOKUPVALUE, but as it returns mutiple rows, it doesn't Work.
I can't make a list of distinct customerIDs, as the A or B is calculated in my model.

How can I get the A or B from my table 1 to my table 2?

Thank you in advance.

 

[Anthonsen]

IF there's a function in PowerPivot, where I can create a new table based on another table i in the model, I could do it.

I get data from a SQL-server to table 1.
In Powerpivot I add a column to table 1, where I calculate AorB. I need all rows to do this calculation.
Now I want to get the string AorB to table 2.

 

[TimRodman]

Why not do this to bring in Table 1?

[COLOR=#333333]SELECT DISTINCT CustomerID
[/COLOR][COLOR=#333333]FROM Table1[/COLOR]

Then calculate AorB in Power Pivot. The advantage is that you will only have one row per customer which allows you to join other tables in Power Pivot to Table 1.

 

[Anthonsen]

Because I need all rows in table 1 to calculate the AorB column (also in table 1).

 

[TimRodman]

Can you take a picture of your actual tables and post them here?

 

[Anthonsen]

Unfortunately no, as it contains confidential data, but I can describe it.

I have two tables. Table 1 have two columns: CustomerID and AorB. AorB is a calculated icolumn in the powerpivot model. Table 2 contains customerID and I could like to add the string from the AorB column in table 1. Both tables have the same customerID in multiple rows.
If it was done in "normal" Excel, I would have done a vlookup, as, vlookup just add the fist values that matches, but this doesn't go with Lookupvalue in Powerpivot

Table 1
CustomerID AorB

1 A
1 A
1 A
2 A
3 B
3 B
4 A
4 A


Table 2

CustomerID
1
1
1
2
3
3
4