Handling Excel formula errors in PQ RRS feed

  • Question

  • Hi,

    I'm using PQ to import and treat some data. I receive the data in an excel spreasheet from a colleague of mine, and oftentimes it comes with some cells containing formula errors (#N/D).

    I'm using PQ because I want to avoid two things:

    - Manual work

    - Running macros to do the dirty work PQ is supposed to do

    But, since the final work is for someone else to use, it would be great to prevent the use of M (i.e.: make this work only with the UI buttons)

    The thing is: I cannot figure out how to make PQ deal with those #N/D! They most often happen in date columns, and when I try to sort or filter this column, I get a DataFormar.Error, which cannot be removed with the Remove Errors command.

    How can I work around this?

    • Edited by vituutiv Thursday, November 10, 2016 5:48 PM
    Thursday, November 10, 2016 5:46 PM


All replies

  • On the Transform tab, choose "Replace errors" and supply the value to replace errors with.
    Friday, November 11, 2016 4:44 AM
  • For some mysterious reason, this doesn't work. Replace Error simply doesn't do anything and, when I use Keep Errors, it returns an empty list.

    I tried to induce a cast type error with this:

    = Table.TransformColumns(#"Tipo Alterado", {{"Column1", each try _ otherwise null , type date}})

    But to no avail - when I try to sort the table, I receive the same message: "Invalid cell value: #N/D"

    Friday, November 11, 2016 1:03 PM
  • Maybe it's not a "real error" then.

    What happens when you convert that column specificly to date - how will these values be shown then?

    Imke Feldmann

    Friday, November 11, 2016 1:38 PM