cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blfox4
New Member

Help with Formula in Power Query - IF(ISNA(VLOOKUP

Hello all,

 

I am fairly new to Power Query but determined to learn it. I have a question about translating an Excel function to Power BI. I've already searched around and came across different DAX solutions and it doesn't seem to be working for me, unless I am doing it wrong, which could be the case, lol.

 

I have several queries but I need to create a new from another query output file where I need to add a column with this formula:

 

=IF(ISNA(VLOOKUP(E:E,'Table2'!, B:B, 2,FALSE)), "No", "Yes")

 

Can someone help me on my way? Thanks in advance!

 

Brooke

7 REPLIES 7
Rocco_sprmnt21
Super User
Super User

image.png

 

PS

wanting to simulate the vlookup function of excel more completely, one would also have to manage the ? and * wildcard characters.

 

 

image.png

 

 

 

image.png

 

 

 

Anonymous
Not applicable

@blfox4 

dax calculated column write below measure:

 

 

 

LookUps=IF(ISBLANK(LOOKUPVALUE(Table2[B],Table2[A],Table1[E], BLANK())),"No","Yes")

 

 

 

Steps in Power Query:

1. Load Table 1

2. Load Table 2

3 Merge these two tables on the basis of column E ( Table 1)  &  Column A of Table 2

4. Now pull the column B from Table 2 by expanding the table.

5. Then write a conditional column: if Table2.B = null then "No" else "Yes".

v-xuding-msft
Community Support
Community Support

Hi @blfox4 ,

 

Just based on the formula, we can't give you a exact solution. Can you please share some sample data and expected results? Then we will understand clearly about your requirement and solve it quickly.  Thanks!

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jimmy801
Community Champion
Community Champion

Hello @blfox4 

 

creating a VLOOKUP in Power Query is not the best way to handle the issue. Yes, it's possible, but you are better of to join both tables, in case transforming the joined table, and then expand the value you need.

 

Hope this helps

 

Jimmy

Greg_Deckler
Super User
Super User

@blfox4 - So, the DAX equivalent to VLOOKUP is LOOKUPVALUE. There is no equivalent to ISNA really in DAX, that's an Excel thing. If you post sample data and expected output I can be more specific. Otherwise, I created an Excel to DAX translation series of blog articles here: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi, 

 

I apologize that I wasn't able to get to this quicker but it wouldn't allow me to share a screenshot earlier. 

 

I have a newer issue that I need to solve before moving from the original one.

 

  • I have the table below that is under my "Merge2". I have my common key of "App Login" that os the same on each of the queries. My second query is "Merge 4" and that contains the same columns, same column headers but I need to add those 30 rows to the "merge2" query but when I go to do that (appending the queries), it creates additional columns versus the added rows with that information filled into the columns that are already there.
  • Before moving to power query, we would take these two tabs and then use am index/match formula as follows:
    =INDEX(PIC!G:G,MATCH('VFID Map'!C:C,PIC!B:B,0))
    • PICG:G is the app login column in my screenshot below (Merge2)
    • VFID Map column is the App Login in my Merge4 
      • I need to pull in 5 different columns into the respective columns in my Merge2 query

Is there a way to do this? I apologize if I am not explaining it too well but I just started using Power Query a week ago and still learning.

 

Thank you very much in advance and please let me know if you need anything else or if I messed up providing the explanation.

 

Thank you,
Brooke

 

blfox4_1-1600222125712.png

 
 
 
 
 

 

 

 

 

 

 

 

Jimmy801
Community Champion
Community Champion

Hello @blfox4 

 

if you need to add rows, use Table.Combine. As you were stating that you already did that, but new columns where created even you have exactly the same columns, then there might be a slighlty difference in the column names (Power query is case sensitive.. so you might check if they are really exactly the same... sometimes you also might have spaces after the name.

Check it out and let us know

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors