Forum Discussion

Aureli's avatar
Aureli
Frequent Visitor
3 years ago
Solved

Power query Replace Values

Hi,

I want to replace the value null for two different values in power query as you can see below:

How can I do it?

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Aureli ,

     

    I think you can add this code in Advanced Editor in Power Query Editor.

    ...    
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column9],Replacer.ReplaceValue,{"De 80-109 kg"})
...

    Sample:

    The whole code in My Sample:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMrIwU4rVATMtjCBMI0MDIM/MyEIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"De 80-109 kg" = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"De 80-109 kg", Int64.Type}, {"Column9", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column9],Replacer.ReplaceValue,{"De 80-109 kg"})
in
    #"Replaced Value"

    Result is as below.


    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    If there are only 2 rows in the table, it's straightforward, remove the column 80-109kg and rename Column9.

    If it's more complex, post some data and any other desired result

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Aureli ,

     

    I think you can add this code in Advanced Editor in Power Query Editor.

    ...    
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column9],Replacer.ReplaceValue,{"De 80-109 kg"})
...

    Sample:

    The whole code in My Sample:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMrIwU4rVATMtjCBMI0MDIM/MyEIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"De 80-109 kg" = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"De 80-109 kg", Int64.Type}, {"Column9", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [Column9],Replacer.ReplaceValue,{"De 80-109 kg"})
in
    #"Replaced Value"

    Result is as below.


    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.