Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I need to do a Lookup value in PBI (like Vlookup in excel) but using two column or Table array and in case has an error type "Error" or anything I want. I think using also OR / IF / IFERROR but i couldn't find a logical combination.
See Example below :
Table 1 | Table 2 | Data Result |
A1 | A2 | 1 |
B1 | B2 | 2 |
C1 | C2 | 3 |
Table | Data result | |
A1 | 1 | |
B1 | 2 | |
C1 | 3 | |
A2 | 1 | |
B2 | 2 | |
C2 | 3 | |
A3 | #N/A | |
B3 | #N/A | |
X | #N/A |
Solved! Go to Solution.
Hi @cristianml,
You can create a calculated column like this: (I suppose the above table is named as 'Table6', the bottom one is named as 'Table7')
Result = IF ( LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ) <> BLANK (), LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ), LOOKUPVALUE ( Table6[Data Result], Table6[Column2], Table7[Table] ) )
Alternatively, you can unpivot 'Table6' to convert its table structure to below:
Then, to do a Lookup value, please use this formula:
Result = LOOKUPVALUE ( 'Table6'[Data Result], 'Table6'[Value], Table7[Table] )
Best regards,
Yuliana Gu
Hi @cristianml,
You can create a calculated column like this: (I suppose the above table is named as 'Table6', the bottom one is named as 'Table7')
Result = IF ( LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ) <> BLANK (), LOOKUPVALUE ( Table6[Data Result], Table6[Column1], Table7[Table] ), LOOKUPVALUE ( Table6[Data Result], Table6[Column2], Table7[Table] ) )
Alternatively, you can unpivot 'Table6' to convert its table structure to below:
Then, to do a Lookup value, please use this formula:
Result = LOOKUPVALUE ( 'Table6'[Data Result], 'Table6'[Value], Table7[Table] )
Best regards,
Yuliana Gu
Thank you, ... Unpivot columns was the most easy way. Thanks !
Why not convert the two columns into one? Make a copy of the table, keep the first column in the first table and the second column in the second, give them the same name then append it?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |