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.
Solved! Go to 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"})
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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 use replace function in PowerQuery, replace null with 685
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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])
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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"})
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Could you illustrate how I would write that? Sorry, I am very VERY new to PowerBI - Thank you!
@Anonymous here is similar post
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Could you illustrate how I would write that? Sorry, I am very VERY new to PowerBI - Thank you!