Power Query - Turning a VLOOKUP from Excel into Power Query

[CNFJDF]

Below is a picture of dummy data for what I am trying to accomplish.
Essentially my company is switching to a new system but the identifying names are broken up differently. In order to get data together to examine it, this "tool" has been created in excel to filter out any unnecessary data not needed from the new program's export. (The export brings A LOT of data so this tool is necessary to reduce time filtering; my team doesn't have the capability to modify the export)
The yellow Helper Column shows us combining the names from the new program together, next to the names from the new program and the old program. The VLOOKUP shows us if it's needed data for our reports by filtering out the N/As that appear.

My question is whether there is a way to do something like this in Power Query or not? I've seen that VLOOKUPs don't seem possible but there are a few work arounds that can be done. I'm still new to Power Query and can't tell if this is one of those work arounds situations.

[v-ssriganesh]

Hi @CNFJDF,
Thank you for reaching out to the Microsoft Fabric Forum Community. I appreciate the helpful insights shared by @Greg_Deckler  and @bhanu_gautam.

@bhanu_gautam  correctly pointed out that you can use Power Query’s Merge Queries feature to achieve this. Power Query is a powerful tool for data transformation and allows you to perform lookups similar to VLOOKUP in Excel.

@Greg_Deckler  also provided an excellent alternative using DAX in Power BI. If you're working with Power BI reports, you can use MAXX(FILTER(..), ..) to perform lookups dynamically within your model. This is useful when working with real-time data inside Power BI rather than preprocessing in Power Query.

• If you’re using Excel or need to clean and preprocess data, Power Query (Merge Queries) is the best approach.
• If you’re working within Power BI and need a calculated column or measure, using DAX is a great alternative.
  
  

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[CNFJDF]

I plan on using Power BI as a possible extension of this project so I will definitely look into MAXX when the time comes. 

Maybe I didn't look at the right information but I wasn't sure if Merge Queries would be quite the right fit. I've tried using it but it keeps putting my information in an obscene amount of columns. I'll look back into it but my exported data is also rows long and keeps saying there's too much data as there are 50ish tests per "dessert type" and there can be anywhere from 10 - 150 "desserts". I only need about 10 tests but this query is being built to help filter out the 40ish tests that are not needed... 

This all makes me wonder if using Power Query is even probable for this situation?

[v-ssriganesh · Accepted solution]

Hi @CNFJDF,
Thank you for your response. It's great to hear that you're considering Power BI as an extension of your project.

Regarding Merge Queries in Power Query, I understand your concern about excessive columns. By default, Power Query brings in all columns from the merged table, but you can remove unnecessary columns after merging by selecting only the required fields.

Since your goal is to filter out 40+ unnecessary tests and keep only the 10 relevant ones, you might find Table.SelectRows or filtering within Power Query more effective than a direct merge. You can apply a filter step after merging to keep only the relevant test rows.

I trust this information proves useful. If it does, kindly Accept it as a solution and give it a 'Kudos' to help others locate it easily.
Thank you.

[CNFJDF]

After playing around with merges and giving it another chance it is definitley the right course. Thanks.

[Greg_Deckler]

@CNFJDF If you can't use PQ then you can certainly use DAX to emulate VLOOKUP. MAXX( FILTER( .. ), .. .) 

[bhanu_gautam]

@CNFJDF , You can use merge in Power Query

https://www.youtube.com/watch?v=73T7isNVH_w