cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bilal_321321
Helper I
Helper I

VLookup help

Hi,

 

I need some help with Power BI pro. I am trying to replicate a vlookup we used in excel which basically is this:


=IFERROR(VLOOKUP(B3*1,Mapping!$P:$Q,2,FALSE),"No Group")

 

I have been able to use Column 1 = RELATED('Report 2'[Group]) which pulls the correct field but on instances where there is no match I want it to display "No Group"

 

Thanks

1 ACCEPTED SOLUTION

 

Hi @Bilal_321321

 

the Lookupvalue formula returns blank if it doesn't have a match in the lookup (lookpvalue formula) try changing your formula to this:

 

Column = SWITCH (
    TRUE (),
    ISBLANK (
        LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
    ), "NoGroup",
    LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
)

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User III
Super User III

Hi @Bilal_321321,

 

You need to do a formula with the following sintax:

 

 

Column =
IFERROR (
    LOOKUPVALUE ( Table[Result], Table[Search_Value], "Value_to_Search" ),
   "No Group"
)

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix,

I must be doing something wrong, the LOOKUPVALUE is working but for instances it's not got a value the "No Group" is not populating. This is the formula i have created so far:

 

Column = IFERROR ( LOOKUPVALUE('Report 2'[Group],'Report 2'[GroupID],'Report1'[GroupID]) , "No Group")

 

The 'Report 2'[Group] is the table and column I'm looking at, 'Report 2'[GroupID] is the linked field I am searching based on 'Report'[GroupID] and the "No Group" should appear instead of blanks.

 

I'm probably making an obvious mistake but can't see it.

 

Thanks

Bilal

 

 

Hi @Bilal_321321

 

the Lookupvalue formula returns blank if it doesn't have a match in the lookup (lookpvalue formula) try changing your formula to this:

 

Column = SWITCH (
    TRUE (),
    ISBLANK (
        LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
    ), "NoGroup",
    LOOKUPVALUE ( Report2[Group], Report2[GroupID], Report1[GroupID] )
)

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Amazing!!! Thanks So much for your help @MFelix. It has worked perfectly

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors