Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

power query - replace value in another column after finding a value in another column

hi,

ref table example below-

rcdCategoryrcdItem
School Fees 
Transport CostFuel
School Fees 
Fees 
Fees 
SalesT-shirts
FeesRegistration fee   
FeesField trip
Transport Cost   Fuel

 

i have a condition where i wish to replace 'fees' in rcdCategory to be 'School Fees' if rcItem is blank.
kindly note that other 'fees' in rcdCategory should remain unchanged.

kindly assist me in writing a power query for this find & replace requirement.

tks & krgds, -nik 

Need Help
Show and Tell
  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    4 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7LCYBADERbCTlrFcIWoN4WD4tGDSxGkti/HxAU9Dgzj+HFiE0/i2QIRIYFAnZFxFbTYquoQyXmRx02ytfyRf+HJuUrtaXNrG5PoqaJzTU5ywIjEcDrIDDlAVx5/TA62Vuq2wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rcdCategory = _t, rcdItem = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"rcdCategory", type text}, {"rcdItem", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"rcdItem"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [rcdCategory] = "Fees" then if [rcdItem] = null then "School Fees" else [rcdItem] else [rcdItem]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"rcdItem"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "rcdItem"}})
    in
    #"Renamed Columns"

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

     

    I would like to add a custom column instead of transform original one, but you can do it

    Table.ReplaceValue(youPreviousStep,each [rcdCategory], each if [rcdCategory] = "Fees" and [rcdItem] ="" then "School Fees" else [rcdCategory],Replacer.ReplaceText,{"rcdCategory"})

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    This M code works

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY6xDYAwDARXiVzDFEgZgKSLKCIwxFKEkW32B6WiALr/vys+JQhzYa7OIyp0AFOXIEre9WAxN7DavfoTayMv8lcOubYSey0kpk9hxI3UJBvx7lbEJ/OEdXEmdPx9mS4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rcdCategory = _t, rcdItem = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"rcdCategory", type text}, {"rcdItem", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [rcdCategory]="Fees" and [rcdItem]="" then "School Fees" else [rcdCategory])
in
    #"Added Custom"

    Hope this helps.

     

5 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7LCYBADERbCTlrFcIWoN4WD4tGDSxGkti/HxAU9Dgzj+HFiE0/i2QIRIYFAnZFxFbTYquoQyXmRx02ytfyRf+HJuUrtaXNrG5PoqaJzTU5ywIjEcDrIDDlAVx5/TA62Vuq2wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rcdCategory = _t, rcdItem = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"rcdCategory", type text}, {"rcdItem", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"rcdItem"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [rcdCategory] = "Fees" then if [rcdItem] = null then "School Fees" else [rcdItem] else [rcdItem]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"rcdItem"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "rcdItem"}})
    in
    #"Renamed Columns"

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

     

    I would like to add a custom column instead of transform original one, but you can do it

    Table.ReplaceValue(youPreviousStep,each [rcdCategory], each if [rcdCategory] = "Fees" and [rcdItem] ="" then "School Fees" else [rcdCategory],Replacer.ReplaceText,{"rcdCategory"})

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    This M code works

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY6xDYAwDARXiVzDFEgZgKSLKCIwxFKEkW32B6WiALr/vys+JQhzYa7OIyp0AFOXIEre9WAxN7DavfoTayMv8lcOubYSey0kpk9hxI3UJBvx7lbEJ/OEdXEmdPx9mS4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [rcdCategory = _t, rcdItem = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"rcdCategory", type text}, {"rcdItem", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [rcdCategory]="Fees" and [rcdItem]="" then "School Fees" else [rcdCategory])
in
    #"Added Custom"

    Hope this helps.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      many tks, StefanoGrimaldi , Anonymous & Ashish_Mathur.

      i have tested all your solutions & all works fine.

      actually there r more similar replacements/corrections that need to be done to the same table in addition to the above case. i have managed to solve them too.

      krgds, -nik