Function returning lookup value from a table

Question

I am trying to build a Power Query function that will return a value from a table looked up based on an input parameter. Eg.

Table:
Name    Team
George  Server
June    DBA

Now I want to supply Name to the function and return the position. Source table is a table defined in the same Excel workbook. I started with that but not sure where I am failing:

(EmployeeName as text) =>

let 
    Source = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    EmployeeRow = Table.SelectRows(Source, each ([Name] = EmployeeName)),    
    Result = Record.Field(EmployeeRow, [Team])
in
    Result

Igor Igor · Accepted Answer · 2019-01-07T19:17:58

Corrected function is:

(EmployeeName as text) =>

let 
    Source = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    EmployeeRow = Table.SelectRows(Source, each Text.Contains([Name],EmployeeName)),
    EmployeeRecord = Table.SingleRow(EmployeeRow),
    Result = Record.Field(EmployeeRecord, "Team")
in
    Result

The problem was that I need to reference column in double quotes and not square brackets in Record.Field() function.

Function returning lookup value from a table

1 Answers