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.
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!
Solved! Go to 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
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
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]?)
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!
Covering 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.