Forum Discussion

Samhunt's avatar
Samhunt
Helper II
2 years ago
Solved

Replace Error

Hi guys,   Is it possible to replace errors in a column in  power query with the value from another column in the same table?     So for example in my case I want every column's error valu...
Need Help
  • Samhunt's avatar
    Samhunt
    2 years ago

    Thank you very much for your replies guys, 

     

    What I did was the following, 

    Replace error with null, 

     

    Then I replaced null with a numerical value that was higher than any other possible value in the column. 

    In my case the values were from 1-10 so the value I added was 110

     

    Then I went to the code and replaced 110 with: each [General rating]

     

    Thanks for your time, 

     

    Have a lovely new year

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Samhunt 

You can refer to the following steps

1.Replace the errors with null

 

2.Replace the null to the general rating value

Table.ReplaceValue(#"Renamed Columns1",each [Service],each if [Service]=null then [General rating] else [Service],Replacer.ReplaceValue,{"Service"})

You can also refer to the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEpVidaCUjIAuEk8C8RCDLBCwP4oFYpmAcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column1", null}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"Column3", null}}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors1",{{"Column2", "General rating"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column3", "Service"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",each [Service],each if [Service]=null then [General rating] else [Service],Replacer.ReplaceValue,{"Service"})
in
    #"Replaced Value"

Best Regards!

Yolo Zhu

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