How to replace multiple values in query editor without creating a new column

[Andrew_na_lang]

Hi Team,

Because my other queries rely on the old format, I need to remove the tray number (Tray: XXX | |) from the column without creating a new column. It was only because the tray number was recently added without my knowledge that some of the queries were broken.

 

[HiraNegi · Accepted solution]

Hi,

You can achieve this by modifying or adding below line of code in the Power Query Advance editor. This will replace the values in the existing column itself.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column1],each if Text.PositionOf([Column1],"||") = -1
then [Column1] else Text.Trim(Text.AfterDelimiter([Column1], "||"))
,Replacer.ReplaceText,{"Column1"})

Below is the sample data and steps.

sample source data:

Final result after adding the replace in Advance Editor:

Complete Advance Editor Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVjA0MlaK1YlWCilKrFSwNDJUqKlRQBaPiHQ2NzNFVmIAUhIRGaVgYW6mFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column1],each if Text.PositionOf([Column1],"||") = -1
then [Column1] else Text.Trim(Text.AfterDelimiter([Column1], "||"))
,Replacer.ReplaceText,{"Column1"})

in
#"Replaced Value"

Hope this helps !!

[FreemanZ]

hi @Andrew_na_lang 

you can always delete the intermediate steps in the last step. or?