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

     

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

     

    • hohlick's avatar
      hohlick
      Continued Contributor
      9 years ago

      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!