Forum Discussion
Power Query - Turning a VLOOKUP from Excel into Power Query
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.
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.
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?
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.After playing around with merges and giving it another chance it is definitley the right course. Thanks.
