Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I need to check if a value of one column exists in another column of the same table.
I need to apply VLOOKUP in Power Query.
With DAX, its possible but with Power Query, I am not able to find a proper solution.
Can someone please help me out?
Solved! Go to Solution.
Hi @Aayushi ,
In Power Query functions can be created like this:
(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
// lookupTable= Products,
// lookupColumnName = "ProductKey",
// returnColumnValue = "Price",
// lookupValue = 1,
colLookup = Table.Column(lookupTable, lookupColumnName),
colToReturn = Table.Column(lookupTable, returnColumnValue),
lookup = List.PositionOf(colLookup, lookupValue, 0),
Result = if lookup >=0 then colToReturn{lookup} else "Not found"
in
Result
Or
You can duplicate your query and merge the two queries to get the desired columns.
Disable the load from the duplicate one to avoid performance issues.
Please refer to the following documents for more information.
#PowerQuery – Replicate doing an Excel VLOOKUP in M – Erik Svensen
How to do a real VLOOKUP (false) in Power Query or Power BI – The BIccountant
VLOOKUP / XLOOKUP in Power Query - Bing video
VLOOKUP in Power Query Using List Functions - Bing video
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Aayushi ,
In Power Query functions can be created like this:
(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
// lookupTable= Products,
// lookupColumnName = "ProductKey",
// returnColumnValue = "Price",
// lookupValue = 1,
colLookup = Table.Column(lookupTable, lookupColumnName),
colToReturn = Table.Column(lookupTable, returnColumnValue),
lookup = List.PositionOf(colLookup, lookupValue, 0),
Result = if lookup >=0 then colToReturn{lookup} else "Not found"
in
Result
Or
You can duplicate your query and merge the two queries to get the desired columns.
Disable the load from the duplicate one to avoid performance issues.
Please refer to the following documents for more information.
#PowerQuery – Replicate doing an Excel VLOOKUP in M – Erik Svensen
How to do a real VLOOKUP (false) in Power Query or Power BI – The BIccountant
VLOOKUP / XLOOKUP in Power Query - Bing video
VLOOKUP in Power Query Using List Functions - Bing video
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @Aayushi
It is call merge in Power Query, check this:
https://learn.microsoft.com/en-us/power-query/merge-queries-left-outer
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |