Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |