• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Query - select row and replace value

lsu8

New Member
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:
NotificationMaterial
190196350350522
1391591null
207712850312392
 
Create a custom column with an If ... Then ... statement , then delete the original material column.
 
Since PQ is case sensitive, the statement goes "if .... then ... else". Using the UI add a conditional column for guidance.
 
Alternately, you can use Table.ReplaceValues.

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

65341

This is implemented through Advanced Editor like below. Where #"Changed Type" indicates previous step.
Code:
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.
 
Back
Top