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.

 

[LloydFinancials]

Anthonsen,

In table 1, does a given single customer have some A's and some B's? If not it seems to be a straightforward lookup. I am not sure I am seeing the whole picture what you mean by find first of many.

=VLOOKUP(customerID,'table 1'!,column#,false)

Luke

 

[Anthonsen]

Hi Luke

Thanks.

I thought this was for PowerPivot questions - sorry it wasn't clear.

The two talbles could look like this:

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

The two tabels are in a PowerPivot Model and I would like to add the AorB from table 1 to each customerID in table 2. AorB are calculated in the model, so it is not possible to add a table from the source groupedby customerID that could be used as lookup table.

Ideas are very welcome

 

[TimRodman]

Can you ever have something like the picture below where a customer can have both A and B values? If not (hopefully not), then I would use Power Query to create a new table from your table that has one row per customer. Then Power Pivot would allow you to draw a relationship to your new table and pickup the AorB value when you need it.

 

[Anthonsen]

Hi

No - it will never happen that a customerID both have a row with A and a row with B.
Using PowerQuery sounds like a good idea, now that I have been reading about it. I have also been looking for powerquery in powerpivot, but can't find it?
Anyone who have a link to a description of that - or maybe easily can explain it?

Thanks.

 

[TimRodman]

Just Google "Power Query Download" to get it. It's free.

For your situation, I would do something like this (click here) with Power Query.

 

[Anthonsen]

Hi

Very cool

Both my tables are already in my powerpivot model - loaded from a SQL-server.
As I add the AorB column using a calculation in the powerpivot model, it is not possible to load it from the SQL-server using Groupby there.

I have PowerQuery in my Excel, but how do I use it on a table that are allready in Powerpivot?

 

[TimRodman]

You have to do Power Query -> Power Pivot rather than Power Pivot -> Power Query. Power Query is your ETL tool, like SSIS. In fact, Power Query will be supported in SSIS 2016, just like Power Pivot became supported in SSAS 2012.

Just use the Only Create Connection and Add this data to the Data Model options that I show in the video in order to send the Power Query results into Power Pivot.

However, if you already know SQL and your data is coming from SQL, I agree with you, just do a SELECT DISTINCT or GROUP BY on the query. I personally don't think Power Query offers a lot in this situation. Where it does offer a lot is when you're pulling from other data sources or you don't already know SQL.

 

[Anthonsen]

Hi

I CANNOT do a select distict, as I need all lines to calculate the AorB column. The column that contains the string I could like to add to table 2.

Any ideas?

Thanks.

 

[TimRodman]

Can't you do this?

SELECT DISTINCT CustomerID,AorB
FROM Table1

Of course you could also do this:

SELECT CustomerID,AorB
FROM Table1
GROUP BY CustomerID,AorB