Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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.TransfromManyList.Select etc., but no way

 

Thanks,

Maxim

Maxim Zelensky
excel-inside.pro
1 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}} ) )

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
IvanBond
Advocate II
Advocate II

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

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 :Smiley Frustrated, 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.

Maxim Zelensky
excel-inside.pro

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!

Maxim Zelensky
excel-inside.pro

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.