Forum Discussion
How to replace null values with a looked up value from another table?
Hi there, my first time on the site and posting 🙂
I'm a beginning user of power query and stuck in a situation where after joins from previous tables and transformations, my combined data set has some records where their Level 4 director didn't join/resolve because they are new employees and aren't in the monthly table yet.
| Level 4 Director | Resource Name |
| Vivek Mallina | Praneeth Pasam |
| null | Samir Ulavapalli |
| Vivek Mallina | Tushar Banthia |
| Paulo Merritt | Tina Somburanasin |
| null | Lisa Shankar |
... I've used a reference query to show me the names of the resources who's names aren't resolving to a Level 4 Director, and I was thinking of creating a separate table to serve as a lookup for replacing the null above, reading something like...
| Resource Name | Level 4 Director |
| Samir Ulavapalli | Vivek Mallina |
| Lisa Shankar | Vivek Mallina |
...so I could use some M-Code or something like it to lookup the null's from the earlier table and end up with these "overridden" values so I don't have null's:
| Level 4 Director | Resource Name |
| Vivek Mallina | Praneeth Pasam |
| Vivek Mallina | Samir Ulavapalli |
| Vivek Mallina | Tushar Banthia |
| Paulo Merritt | Tina Somburanasin |
| Vivek Mallina | Lisa Shankar |
...and help or hints would be GREATLY appreciated!
2 Replies
NewStep=Table.ReplaceValue(PreivousStepName,each [Resource Name],"",(x,y,z)=>ReferenceTable{[#"Resource Name"=y]}?[Level 4 Director]? ??x,{"Level 4 Director"})
