cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Anonymous
Not applicable

PS

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

Anonymous
Not applicable

dax calculated column write below measure:

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

Steps in Power Query:

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".

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.
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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
New Member

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

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors