Custom column using VLookup?

[Syndicate_Admin]

Hello all!

I have been struggling with this for a while, I hope someone can help out.
I have a situation where I have a Power Query / dataset 'dataSet', that can be refreshed with monthly new data. Part of this data set is:

Ref number	Owner
100	Jane
101	Peter
102	Frank
103	Jane
104	Frank
105	Frank

Then inside a separate worksheet, I have manually entered the following 'static' additional information:

Name	Department
Frank	Finance
Jane	Sales
Peter	Finance

I would love for a Power Query method to add a custom column in the existing query, that looks at the name and fills the custom column with the corresponding department. It should then look something like this:

Ref number	Owner	Department
100	Jane	Sales
101	Peter	Finance
102	Frank	Finance
103	Jane	Sales
104	Frank	Finance
105	Frank	Finance

In normal excel terms I'd use VLookup. Could anyone help me how to do this using M formula language?

Many thanks in advance!

[AlexisOlson · Accepted solution]

In Power Query, you can do this with a merge. The documentation is helpful for this:
https://docs.microsoft.com/en-us/power-query/merge-queries-overview

[wdx223_Daniel]

say, Table1 is your dataset and Table2 is your static table and the column of Name is unique, try this code

NewStep=Table.AddColumn(Table1,"Department",each Table2{[Name=[Name]]}?[Department]?)

[Syndicate_Admin]

Hi Syndicate,

Thanks, I've tried it but it didn't quite work yet. I filled in what I believe is the table names, which I made as connection only queries:
NewStep = Table.AddColumn(#"Replaced Value","Department",each #"Name vs Dept"{[Unique names=[Unique names]]}?[Department]?)

"'Replaced Value" = the query where the new column should come with the data present of the first table in my original post

"Name vs Dept" = the connection query containing the columns "Unique names" and "Department".

Is it right to make this table I have on the different worksheet into a query in order to use it?

Thanks again!