Power Query - select row and replace value

[lsu8]

Clean-up is needed on historical data in order to correctly apply graphics. I need assistance in transforming data in Power query -- where Notification = '1391591' update [Material] to '51111807'. Here's a sample data set:

Notification	Material
1901963	50350522
1391591	null
2077128	50312392

 

[AliGW]

Create a custom column with an If ... Then ... statement , then delete the original material column.

 

[GraH - Guido]

Since PQ is case sensitive, the statement goes "if .... then ... else". Using the UI add a conditional column for guidance.

 

[Chihiro]

Alternately, you can use Table.ReplaceValues.

Ex:

= Table.ReplaceValue(#"Changed Type",each [Material], each if [Notification] = 1391591 then 51111807 else [Material],Replacer.ReplaceValue,{"Material"})

This is implemented through Advanced Editor like below. Where #"Changed Type" indicates previous step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcqxDcAgDATAXVxT+N9yyM+C2AClY/9Y0J5uDIMcesKapUd6kjZbcQgpFH97rUP03sH3TjBUc/4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Notification = _t, Material = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Notification", Int64.Type}, {"Material", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Material], each if [Notification] = 1391591 then 51111807 else [Material],Replacer.ReplaceValue,{"Material"})
in
    #"Replaced Value"

This can be faster in execution. Though if you are not comfortable editing M code in Advanced Editor, I'd recommend going with method described by Ali and GraH as it can be done purely through GUI.