You’re offline. This is a read only version of the page.
Announcements
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!
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!
Share
Report
All responses (
Answers (
739
