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

Update a value based on another value

I have a table and there is a null based on a missing persons name.  I want to update null with a value.  This table contains an id and name. The only person I didn't have in a table with ID 685.  I want to update Forecast Manager field with the name but only for ID 685 because the rest are fine.   This person is not an employee therefore they aren't in our HR System so this is a manually name I have to enter to the model.

Need to update values.png

1 ACCEPTED SOLUTION

@Anonymous this will do it

 

= Table.ReplaceValue(#"Reordered Columns",each [Forecast Manager],
each if [Forecast Manager Id] = 685 and [Forecast Manager] = null then 685 else [Forecast Manager],
Replacer.ReplaceValue,{"Forecast Manager"})





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

14 REPLIES 14
parry2k
Super User
Super User

@Anonymous use replace function in PowerQuery, replace null with 685

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Thanks but Replace value replaces all nulls I only need to update the null for ID 685  Forecast Manager ID 685 needs a name in the Forecast Manager field.

Anonymous
Not applicable

I tried to follow that logic but it doesn't like my attempt:

 

#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"ID", "Name", "Period", "Year", "Created", "Updated", "Forecast Manager ID", "Forecast Manager", "Submitted", "ResourceAllocation", "Report Period"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns", each if Text.Contains([Forecast Manager ID],"685") then "Rita Carnes" else [Forecast Manager])
in
#"Replaced Value"

@Anonymous are you getting an error? Without knowing what error you are getting, it is hard to imagine what is going on.

 

What is data type for your [Forecast Manager Id] column?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Expression.Error: 2 arguments were passed to a function which expects 5.
Details:
Pattern=
Arguments=List

@Anonymous did you followed the post which i sent earlier on how to replace with condition?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

I did read it so tried to write it myself based on that.   Can I do it  from the Replace Window?

 

It's really simple 

Replace Value 

 

Value to Find :   Find Forecast Manager ID = 685

Replace With:  Rita Carnes

 

 

@AnonymousThis is what is given in the post

 

  #"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [Gender],each 
if [Surname] = "Manly" then "Male" else [Gender],Replacer.ReplaceValue,{"Gender"})

and this is what you have in the code based on what you pasted earlier it is totally different, can you use above and replace your column name and condition

 

#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",
each if Text.Contains([Forecast Manager ID],"685") then "Rita Carnes" else [Forecast Manager])

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@Anonymous this will do it

 

= Table.ReplaceValue(#"Reordered Columns",each [Forecast Manager],
each if [Forecast Manager Id] = 685 and [Forecast Manager] = null then 685 else [Forecast Manager],
Replacer.ReplaceValue,{"Forecast Manager"})





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Thanks - That worked!

@Anonymous you can put the if condition in replace formula to replace it only for the value you are interested. Here is similar post on this.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Could you illustrate how I would write that?  Sorry, I am very VERY new to PowerBI - Thank you!

 

@Anonymous here is similar post






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Could you illustrate how I would write that?  Sorry, I am very VERY new to PowerBI - Thank you!

 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors