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

     

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!