Removing errors from list (not column) - in Power Query 'M'

[hohlick]

Hi All,

cannot resolve some issues with lists containing errors in Power Query (Query Editor).

Suppose I have a list named ListWithError:

ListWithError = {"01-01-2016", 1, Date.From("this is not a date"), #date(2016,3,31)}

It equals:

01-01-2016

1

Error

3/31/2016

I would like to get a list of only dates, which (in case there are no errors in list elements) I can get with

ListOfDates = List.RemoveNulls(List.Transform(ListWithNoErrors, each try Date.From(_, "en-US") otherwise null))

But if there is an error in list, I cannot do this way, I got only one element in list

How can I remove element with error from list without transforming given list to column and applying Table.RemoveRowsWithErrors?

Tried List.Transform and List.TransfromMany, List.Select etc., but no way

Thanks,

Maxim

[IvanBond]

Hi Maxim,

I think, you have to protect your data before you get errors. I mean, when you get data from some source, don't apply ChangeType or Date.From without (try..otherwise) statement (e.g. try Date.From("this is not a date") otherwise -1).

I spent sometime on the problem, but as List.RemoveErrors is not available, couldn't find workaround using other functions.

BR,
Ivan

[hohlick]

Hi Ivan!

Thanks for advise.

Unfortunately, source of error could be any (for example, error like =NA() taken from Excel table). As I cannot control user input, cannot filter errors out of list directly and there are no List.RemoveErrors :, the only way I see now is to use this code as function or as raw code:

(ListWithErrors as list)=>
let
    CleanList = Table.RemoveRowsWithErrors(Table.FromColumns({ListWithErrors}))[Column1]
in
    CleanList

it is the shortest way i found, but still curious does it has best performance when operating with relatively large lists.

[OwenAuger · Accepted solution]

@hohlick @IvanBond

Here are two further ideas using List.Positions to index the original list, but I don't think they are necessarily any more elegant and not sure performance-wise:

=
List.RemoveNulls(
    List.Transform(
        List.Positions( ListWithError ),
        each try ListWithError{_} otherwise null
    )
)

=
List.Accumulate(
    List.Positions( ListWithError ),
    {},
    (CleanListSoFar, CurrentPosition) =>
        CleanListSoFar &
        (if
            (try ListWithError{CurrentPosition})[HasError] then {} else {ListWithError{CurrentPosition}}
        )
)

[hohlick]

Hello @OwenAuger!

Thanks a lot!

Your solutions looks very interesting. 

Personally I'll take second solution for my purposes, as it is more universal (for example, whether I need remove errors only but not nulls).

But first solution is also very interesting because we can replace errors with other values (not nulls), and after some face-lifting this could be a very useful function like List.ReplaceErrors:

(ListWithError as list, optional Replacement as any) as list =>
let
    Source = List.Transform(List.Positions(ListWithError), each try ListWithError{_} otherwise Replacement)
in
    Source

Thanks again for the idea!