cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 DirectorResource Name
Vivek MallinaPraneeth Pasam
nullSamir Ulavapalli
Vivek MallinaTushar Banthia
Paulo MerrittTina Somburanasin
nullLisa 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 NameLevel 4 Director
Samir UlavapalliVivek Mallina
Lisa ShankarVivek 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 DirectorResource Name
Vivek MallinaPraneeth Pasam
Vivek MallinaSamir Ulavapalli
Vivek MallinaTushar Banthia
Paulo MerrittTina Somburanasin
Vivek MallinaLisa Shankar


...and help or hints would be GREATLY appreciated!

2 REPLIES 2
wdx223_Daniel
Super User
Super User

NewStep=Table.ReplaceValue(PreivousStepName,each [Resource Name],"",(x,y,z)=>ReferenceTable{[#"Resource Name"=y]}?[Level 4 Director]? ??x,{"Level 4 Director"})

Anonymous
Not applicable

Thank you wdx223_Daniel! I'll try it out right away!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors