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

m_dekorte's avatar
m_dekorte
Resident Rockstar
2 years ago

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.