Forum Discussion

Mohan128256's avatar
Mohan128256
Icon for Helper IV rankHelper IV
3 years ago

Replace numbers with Text in Power Query

Hi All, I have a sample table data where I am trying to replace 0 with N and 1 with Y using Replacevalue in power query. I cannot add as new custom column because, the same operation i should do on more than 40 columns. Table.

 

 

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeNAIDZQitWJVnICstzdgUQ4EBuChZyBLDc3IOEKF3IBsjw8UDSCjAkJQdHoBBNyhaiKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type", each [Column3], each if ([Column1]="A" or [Column1]="B") and [Column2]="TT" then "change" else [Column3], Replacer.ReplaceValue, {"Column3"}),
    Custom2 = Table.ReplaceValue(
     Custom1, 
     each [Column4] = 0, 
     each "N", 
     (x,y,z)=> if y then z else x,
     {"Column4"} 
)
in
    Custom2

 

 

 

 

But i need to replace both 0 with N and 1 with Y in single power query step only.

 

Can anyone please help me on this.

 

Thanks,

Mohan V.

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Mohan128256 , Check if this code can help

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJQitWJVnICsgzBLGe4mAtELBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","0","N",Replacer.ReplaceText,{"Column2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","1","Y",Replacer.ReplaceText,{"Column2"})
    in
    #"Replaced Value1"

    • Mohan128256's avatar
      Mohan128256
      Icon for Helper IV rankHelper IV
      3 years ago

      amitchandak 
      like i mentioned, i have more than 40 columns to do the replace values with same.

      I found a solution for this as below which works fine for me in a single step

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeNAIDZQitWJVnICstzdgUQ4EBuChZyBLDc3IOEKF3IBsjw8UDSCjAkJQdHoBBNyhaiKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type", each [Column3], each if ([Column1]="A" or [Column1]="B") and [Column2]="TT" then "change" else [Column3], Replacer.ReplaceValue, {"Column3"}),
    #"Duplicated Column" = Table.DuplicateColumn(Custom1, "Column4", "Column5"),
    Custom2 = List.Accumulate( {"Column4", "Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "0" then "N" else if Text.From( Record.Field( _, a)) = "1" then "Y" else _, Replacer.ReplaceValue, {a} ))
in
    Custom2

       

      but only thing now i need to know how can i do it on date data type columns.

       

       

      here 1900-01-01 and 2000-01-01 should be replaced as null else return the dates.

      I have tried this below but didnt work

          Custom3 = List.Accumulate( {"Column2"}, Custom2, (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1900-01-01" then null else if Text.From( Record.Field( _, a)) = "2000-01-01" then null else _, Replacer.ReplaceValue, {a} ))

       

      any help.