Power Query: Check the List
6 November 2019
Welcome to our Power Query blog. This week, we look at ‘remove error’ for lists.
The next screen shows a very simple list with just one problem:
There is an error. My ‘Manage Items’ section allows me to add and remove items (I have expanded the latter to show that errors are not an option), or remove duplicates – but there is no function for removing errors.
One way I can tackle this is to ‘Convert to Table’ and transform my data.
I accept the defaults.
Now, when I look at the options to ‘Remove Rows’, I can see that I can ‘Remove Errors’ if I wish. I do this.
On the ‘Transform’ tab, I have the option to ‘Convert to List’:
I now have my list without the errors, but it has taken three steps to achieve this:
#"Converted to Table" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Converted to Table", {"Column1"}),
Column2 = #"Removed Errors"[Column1]
I’d like to be able to do this in one step. In order to achieve this, I need to use three List() M code functions. The first one is:
List.Transform(list as list, transformation as function) as list
This performs the transformation on each item in the list and returns the new list.
In this case, the transformation will use the next M List() function:
List.Positions(list as list) as list
This function returns a list of offsets for the input list. When using List.Transform to change a list, the list of positions can be used to give the transform access to the position.
Finally, I will use List.RemoveNulls() to tidy up my list.
List.RemoveNulls(list as list) as list
This removes all occurrences of null values in the list. If there are no null' values in the list, the original list is returned. (Ideally there would be a List.RemoveErrors() that looks a lot like this, but not yet!)
I will combine these functions to give the following step:
= List.RemoveNulls(List.Transform(List.Positions(Column1),each try Column1{_} otherwise null))
(Column1 is the previous step defining my list.)
This has removed the error in one step without needing to convert my list to a table.
Come back next time for more ways to use Power Query!
