Possible to return error values in PQ?

[cr731]

I am using Power Query to review an Excel file and look for any error values (such as #NA , #VALUE , etc.).

When PQ imports these, it converts the cell to Error. If returned to the Excel worksheet, these cells become null.

I'd like to know if it's possible to have PQ actually return the cell value. Essentially I would want to keep the rows with errors and then return those exactly as they were input to show the user where there are errors. But it seems like PQ won't import #NA and allow it to be returned in a query.

 

[VBA Geek]

You can add a custom column that extracts the error

for instance, if you have a table with just two columns: Name and Age. And Age could potentially contain #N/A errors, you could do:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],


    AddCol = Table.AddColumn( Source, "AgeErrorChechk",
                                                each
                                                let
                                                    T = try [Age]
                                                in
                                                    if T[HasError] = true then T[Error][Message] else T[Value]
                                            
                           )
in
   AddCol

 

[cr731]

Thanks; that is exactly what I needed.