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"})
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
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?
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?
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])
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"})
Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks - That worked!
Could you illustrate how I would write that? Sorry, I am very VERY new to PowerBI - Thank you!
Could you illustrate how I would write that? Sorry, I am very VERY new to PowerBI - Thank you!
User | Count |
---|---|
123 | |
63 | |
56 | |
47 | |
42 |
User | Count |
---|---|
113 | |
65 | |
61 | |
56 | |
45 |