Replace Error

[Samhunt]

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 value replaced with the value in the general rating column. 

Regards

[Samhunt · Accepted solution]

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]

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.

[m_dekorte]

Hi @Samhunt ,

You can give this custom replError function a go.

It takes 2 argument values, first the table to transform and second the column name that contains the value to replace any errors by. As illustrated in this query.

let
  Source = Table.FromColumns(
    {{1 .. 9}, {"A" .. "E", 1 & 2, "G" .. "I"}, {"a" .. "e", 1 & 2, "g" .. "i"}}
  ), 
  replError = (tbl as table, replByCol as text) =>
    Table.FromRecords(
      Table.TransformRows(
        tbl, 
        each Record.FromTable(
            Table.ReplaceErrorValues(Record.ToTable(_), {"Value", Record.Field(_, replByCol)})
          )
      )
    ), 
  t = replError(Source, "Column1")
in
  t

here's the input table

and the return value, after invoking the custom function

I hope this is helpful.

[PijushRoy]

Hi @Samhunt 

You are getting error after change type to Whole Number.
Can you please share screenshot of the table, before change datatype?

[zenisekd]

@Samhunt , use this procedure, only instead of replace value use replace error:
https://wmfexcel.com/2021/09/04/how-to-replace-value-with-a-value-from-another-column-in-power-query...

Kudos and mark as solution appreciated.