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

[blfox4]

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

[Anonymous]

PS

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

[Anonymous]

@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]

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!

[Jimmy801]

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]

@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

[blfox4]

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

[Jimmy801]

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