Replace Error

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.

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.

Hi Samhunt 

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

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.