# Powerpivot Lookup



## amotto11 (Feb 14, 2012)

Hi All,

I am basically trying to do a simple vlookup in PowerPivot. I am aware that powerpivot relationships act like a vlookup, but i am not trying to put my data into a pivot table, i am trying to keep everything in the PowerPivot table. here is an example of what i am trying to accomplish:

Table 1

Column 1 Column 2
A 1
B 2
C 3
D 4
E 5
F 6

Table 2

Column 1
A
B
C
G
H
Z

I would like to create an if(isna(vlookup)),0,1) statement in column 3 of Table 1. in excel the statement would be =if(isna(vlookup(Table1A in column1,1,False)),1,0). this would put a 1 in column 3 if A was found in column 1 of table 2, if it was not found it would put a zero. This way i could filter by the 1's in column three and get only that data(which i need). I cannot do this because like i said i have over 9 million rows which excel cannot handle. can anyone help me on this problem. Again i cannot create a pivot table like this and compare the information because it too would be over 2 million rows. that is why i need to be able to just filter the powerpivot table and grab the information that i need. I believe the formula i need is to create a relationship between the two column 1's and put in column three of table one, =if(related(column 1 in table 2)=cellA1 in table 1,1,0) although this formula is having some problems.

Any help would be greatly appretiated,

Thanks


----------



## powerpivotpro (Feb 15, 2012)

Just to make sure, if you do have a relationship created, have you tried =RELATED()?  That is the VLOOKUP equivalent in PowerPivot (once a relationship is in place).


----------



## amotto11 (Feb 15, 2012)

powerpivotpro said:


> Just to make sure, if you do have a relationship created, have you tried =RELATED()? That is the VLOOKUP equivalent in PowerPivot (once a relationship is in place).


 

I have tried that, as i said in my original post i tried an if(related()) statement but i have no way of knowing that it was correct or not. it did put some ones in which is good but i am not sure if it put them all in. do you think that my if(related()) statement in the last line of my original post is working?


----------



## ruve1k (Feb 15, 2012)

I think your are using the wrong tool to accomplish what you want. I believe you should be using SQL.
What is the relationship between Table1 and Table2? Which table has distinct values in column1?


----------



## amotto11 (Feb 15, 2012)

ruve1k,

Thank your for your response,

I am sorry but i am not sure where to even begin on sql, i am not familiar with it at all.

I am using Table 2 as my lookup table in other words it is the table with only distinct values. Table one has repeating values in column 1.


----------



## ruve1k (Feb 15, 2012)

Try to emulate the following SQL to obtain your data from the source. 
	
	
	
	
	
	



```
SELECT Column1, Column2
FROM Table1
WHERE Column1 IN (SELECT Column1
                  FROM Table2
                  GROUP BY Column1)
```

For Table1[Column3] you could use something like: 
	
	
	
	
	
	



```
=ISTEXT(RELATED(Table2[Column1]))*1
```


----------



## powerpivotpro (Feb 15, 2012)

Hey amotto, are the columns of A, B, C, D, etc. in your two tables unique?  Meaning, can a single value like A appear more than once?


----------



## ruve1k (Feb 15, 2012)

Rob, I think he mentioned that.


amotto11 said:


> I am using Table 2 as my lookup table in other words it is the table with only distinct values. Table one has repeating values in column 1.


----------



## powerpivotpro (Feb 15, 2012)

Well crap I can't read today apparently 

I do not understand at all why the =RELATED() function isn't working then.  This should work fine.

=IF(ISBLANK(RELATED(Table2[Column1)),0,1)

Does that work?


----------



## amotto11 (Feb 15, 2012)

powerpivotpro said:


> Hey amotto, are the columns of A, B, C, D, etc. in your two tables unique? Meaning, can a single value like A appear more than once?


 
In table 1 column 1 has repeating values (A is listed more than once). Table 2 only contains column 1, in which there are no repeating values, hence it is acting like my lookup table. I am wanting to check and see if the values from table 1 column 1 are in table 2 column 1.


----------



## amotto11 (Feb 14, 2012)

Hi All,

I am basically trying to do a simple vlookup in PowerPivot. I am aware that powerpivot relationships act like a vlookup, but i am not trying to put my data into a pivot table, i am trying to keep everything in the PowerPivot table. here is an example of what i am trying to accomplish:

Table 1

Column 1 Column 2
A 1
B 2
C 3
D 4
E 5
F 6

Table 2

Column 1
A
B
C
G
H
Z

I would like to create an if(isna(vlookup)),0,1) statement in column 3 of Table 1. in excel the statement would be =if(isna(vlookup(Table1A in column1,1,False)),1,0). this would put a 1 in column 3 if A was found in column 1 of table 2, if it was not found it would put a zero. This way i could filter by the 1's in column three and get only that data(which i need). I cannot do this because like i said i have over 9 million rows which excel cannot handle. can anyone help me on this problem. Again i cannot create a pivot table like this and compare the information because it too would be over 2 million rows. that is why i need to be able to just filter the powerpivot table and grab the information that i need. I believe the formula i need is to create a relationship between the two column 1's and put in column three of table one, =if(related(column 1 in table 2)=cellA1 in table 1,1,0) although this formula is having some problems.

Any help would be greatly appretiated,

Thanks


----------



## ruve1k (Feb 15, 2012)

Rob, when I initially tried ISBLANK it returned FALSE even for rows with no match in Table2.  Any explanation for that?


----------



## powerpivotpro (Feb 15, 2012)

Interesting.  I wonder if IF = "" works?


----------



## amotto11 (Feb 15, 2012)

sorry for the post, meant to post elsewhere, can't figure out why it posted here.


----------



## ruve1k (Feb 15, 2012)

To clarify:
=ISBLANK(RELATED(Table2[Column1]))  returns FALSE for every row

But if you do it in two calculated columns then it works.
So if you first create RELATED_VAL:=RELATED(Table2[Column1]) and then create TryISBLANK:=ISBLANK([RELATED_VAL]) the it'll return TRUE for rows in which RELATED_VAL is BLANK.


----------



## ruve1k (Feb 15, 2012)

amotto11 said:


> if the format of my data was numbers instead of text my first equation =if(related(table2[column1])=table1[A1],1,0) would work. if my data was in text as it is in my example ruve1k's formula worked. My data is in whole numbers but when i changed the format to text and used ruve1k's formula, as i stated i obtained my original number of 1's and 0's. therefore my original equation will work for whole numbers while ruvek1's formula will work for text.


amotto11,
If you're working with numbers then just use ISNUMBER instead of ISTEXT.


----------



## amotto11 (Feb 15, 2012)

I believe that the formula ruve1k gave me worked. the number of one's that i obtained in the final outcome was the same number that i had originally obtained from my suggested formula that i was not sure worked or not. That seems to tell me that my first equation along with this one was correct. if the format of my data was numbers instead of text my first equation =if(related(table2[column1])=table1[A1],1,0) would work. if my data was in text as it is in my example ruve1k's formula worked. My data is in whole numbers but when i changed the format to text and used ruve1k's formula, as i stated i obtained my original number of 1's and 0's. therefore my original equation will work for whole numbers while ruvek1's formula will work for text. Thank you all very much for your help!


----------



## amotto11 (Feb 15, 2012)

I believe that ruvek1's formula worked. the number of one's that i obtained in the final outcome was the same number that i had originally obtained form the formula i wasn't sure was working in my original post. That seems to tell me that my first equation along with this one was correct. if the format of my data was numbers instead of text my first equation =if(related(table2[column1])=table1[A1],1,0) would work. if my data was in text as it is in my example ruve1k's formula worked. My data is in whole numbers but when i changed the format to text and used ruve1k's formula, as i stated i obtained my original number of 1's and 0's. therefore my original equation will work for whole numbers while ruvek1's formula will work for text. Thank you all very much for your help!


----------



## ruve1k (Feb 15, 2012)

FYI, I filed a bug report on SQL Server Connect regarding how the ISBLANK function is working. I also attached a sample file which may take some time to show up there. Please review the item and vote and comment on it.
https://connect.microsoft.com/SQLServer/feedback/details/725090/when-isblank-not-blank#details


----------



## ruve1k (Apr 16, 2012)

To follow up on this item, it seems that there was in fact a bug in PowerPivot regarding how ISBLANK(RELATED(Table2[Column1])) returns FALSE even for a missing value (referential integrity violation).
To quote from the latest comments by Microsoft: 


> Scenario 3 (the workbook that you sent us):
> The workbook that you provided contains various calculated column expressions as follows:
> ISBLANK_RELATED:=ISBLANK(RELATED(Table2[Column1]))
> RELATED_VAL:=RELATED(Table2[Column1])
> ...


https://connect.microsoft.com/SQLServer/feedback/details/725090/when-isblank-not-blank


----------

