Forum Discussion

Mohan128256's avatar
Mohan128256
Helper IV
3 years ago
Solved

Replace Numbers to 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 m...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi Mohan128256 ,

     

    Try this:

    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],
            each if [Column4] = 0 then "N" else if [Column4] = 1 then "Y" else null,
            Replacer.ReplaceValue,
            {"Column4"}
        )
        
in
    Custom2

     

    For this output:

     

    Pete

m_dekorte's avatar
m_dekorte
Resident Rockstar
3 years ago

Hi Mohan128256 ,

 

You can give this a go.

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

 

You can provide it a list with column names and it will replace both 0 and 1's.

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

Mohan128256's avatar
Mohan128256
Helper IV
3 years ago

the code is bit complicate to understand.

But still thanks for the solution.