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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.