Forum Discussion

hohlick's avatar
hohlick
Continued Contributor
9 years ago
Solved

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

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

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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}}
        )
)

     

4 Replies

  • IvanBond's avatar
    IvanBond
    Advocate II
    9 years ago

    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's avatar
      hohlick
      Continued Contributor
      9 years ago

      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 ::smileyfrustrated:, 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's avatar
        OwenAuger
        Super User
        9 years ago

        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}}
        )
)