Powerpivot Lookup

amotto11

New Member
Joined
Feb 14, 2012
Messages
10
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
 
Last edited:

ruve1k

Board Regular
Joined
Aug 31, 2008
Messages
171
Rob, when I initially tried ISBLANK it returned FALSE even for rows with no match in Table2. Any explanation for that?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

amotto11

New Member
Joined
Feb 14, 2012
Messages
10
sorry for the post, meant to post elsewhere, can't figure out why it posted here.
 
Last edited:

ruve1k

Board Regular
Joined
Aug 31, 2008
Messages
171
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

Board Regular
Joined
Aug 31, 2008
Messages
171

ADVERTISEMENT

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

New Member
Joined
Feb 14, 2012
Messages
10
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

New Member
Joined
Feb 14, 2012
Messages
10

ADVERTISEMENT

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

Board Regular
Joined
Aug 31, 2008
Messages
171
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])
ISBLANK:=ISBLANK([RELATED_VAL])

In this case ISBLANK(RELATED(Table2[Column1])) returns FALSE for RI (referential integrity) violations, while ISBLANK([RELATED_VAL]) returns TRUE for RI violations. The former is indeed a bug in our product. The good news is that we have fixed this bug in current builds, and the next TAP/Beta release will have that fix.
https://connect.microsoft.com/SQLServer/feedback/details/725090/when-isblank-not-blank
 

Watch MrExcel Video

Forum statistics

Threads
1,108,741
Messages
5,524,560
Members
409,584
Latest member
Devil_717

This Week's Hot Topics

Top