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!