Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good afternoon. I am working through modifying a vlookup for power query and am runninginto issues.
Goal: Fill in mgr grade_rank with the manager's grade rank.
Method: Match mgr_emplid and Employee ID columns and return the value in the grade_rank column.
In the below example, I matched the mgr_emplid "1313" with the Employee ID "1313" and returned the value of "5" to mgr grade_rank.
I know how to do it in excel, but am struggling to do it in powerquery as Lookup and Match functions. Any help would be appreciated.
Current version:
Solved! Go to Solution.
THe easiest way to do this in Power Query is to merge the table with itself, but merge the Manager ID column with the Employee ID coluimn, then expand the grade.
Then expand that field only.
I see your example is using LOOKUPVALUE() - that is a DAX function, and you can use this as a calculated column:
Column =
LOOKUPVALUE(
[Grade],
[EmployeeID],
[mgrID]
)
It will return this:
But I recommend against it. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Avoid the temptation to work in the Data view and create a bunch of calculated columns like you would think of when using Excel when you just add another column. Power BI is very different and that is not a good practice to get into. Try to use Power Query for transformations and bringing in new data, and Measures for data analysis. Avoid Calculated Columns where possible.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Anonymous , refer
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
https://www.myonlinetraininghub.com/excel-power-query-vlookup
try to adapt this code
THe easiest way to do this in Power Query is to merge the table with itself, but merge the Manager ID column with the Employee ID coluimn, then expand the grade.
Then expand that field only.
I see your example is using LOOKUPVALUE() - that is a DAX function, and you can use this as a calculated column:
Column =
LOOKUPVALUE(
[Grade],
[EmployeeID],
[mgrID]
)
It will return this:
But I recommend against it. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Avoid the temptation to work in the Data view and create a bunch of calculated columns like you would think of when using Excel when you just add another column. Power BI is very different and that is not a good practice to get into. Try to use Power Query for transformations and bringing in new data, and Measures for data analysis. Avoid Calculated Columns where possible.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |