Forum Discussion

Andrew_na_lang's avatar
Andrew_na_lang
Helper V
3 years ago
Solved

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

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.

 

Need Help
  • HiraNegi's avatar
    HiraNegi
    3 years ago

    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 !!

2 Replies

  • HiraNegi's avatar
    HiraNegi
    Resolver II
    3 years ago

    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 !!