Errors not identified with 'try otherwise'

[Anonymous]

Hello all,

I have a query that I'm using to get information from pdfs stored online. As some of the pdf files have a different format, I'm using error tracking to account for this. However, I have a specific issue around a certain command not being picked up as an error.

My code has the structure:

try
let
   Source = Pdf.Tables(Web.Contents(URL)) # My URL parameter I'm using to get multiple pdf files
   Table001 = Source{[Id = "Table001"]}[Data]
   TableFinal = Table.PromoteHeaders(Table001, [PromoteAllScalars=true])
   TableFiltered = Table.SelectRows(FullTable, each ([COL1] = null))
in
   TableFiltered

So the error comes in the row 'TableFiltered', sometimes the pdfs don't read correctly and the column "COL1" doesn't exist. So an error is thrown. I have tried to catch this in a number of different ways but the problem is that Power Query never recognises that an error is thrown. This is best exemplified if I use the code

TableFiltered = try Table.SelectRows(FullTable, each ([COL1] = null))

 It would return the error table where 'HasError' is False and there is a table in the 'Value' component (apologies, can't get an image to embed).

If I click through to the Table, it would then throw me an error that "COL1" could not be found , this is the case if I try lots of different options:

TableFiltered = Table.SelectRows(FullTable, try each ([COL1] = null))

 In the end I tried explicitly throwing an error with

TableFiltered = Table.SelectRows(FullTable, try each (try [COL1] = null otherwise error "AN ERROR!!!"))

 And encapsulating the whole function with 'try and otherwise' however, the function still returns a table which makes it impossible to remove from my full function with the 'remove errors' function.

As a result, my query never completes and I cannot get at the rest of the data. I don't want to do a hack that manually removes this record as it may be a wider issue. Other errors are picked up with 'try' without any issues. What's going on here?

My latest thinking is that the error is being encapsulated in a table due to the 'each' command, however, I've tried working around this and I can't get another result.

Thanks,

[dax]

Hi Wiredchop, 

It seems that the error function is used for cell or exist column to show error when transforming. You could refer to errors  and Error-handling in Power Query for details.

And in your condition, [col1] might not exist, so I think it might not apply on this.

Best Regards,
Zoe Zhi

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