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!

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

This is actually a better answer, depending on your interpretation of "the same operation i should do on more than 40 columns".

If you interpret this as doing exactly the same thing to 40 columns, then this solution will allow you to list all the columns in one go, whereas my solution, as it is, won't.

Depending on the nature of the table data, this solution could be immensely powerful: you could potentially just feed it Table.ColumnNames and smash the whole table in one go, if every column fits the same data pattern. 👍

 

Pete

  • Mohan128256's avatar
    Mohan128256
    Helper IV
    3 years ago

    BA_Pete m_dekorte how do we can manage the same for date data type columns?

    here 1900-01-01 and 2000-01-01 should be replaced as null else 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.