PQ/M working with list syntax - List.RemoveItems with criteria

[Syndicate_Admin]

Hi all,

Using PQ to transform text data where a particular column can contain excessive characters (over the 32k mark). The column in question is a dump of activity logging.

There is a pattern in the string that means that I can replace common values to inject delimiters - enough to then turn this single cell of text into a LIST object, where each item in the list is a type of activity and easy to read.

Where I'm stuck seems really simple, but I can't find the method or syntax to use functions that seem appropriate; e.g. List.RemoveItems or List.Select but where the item meets a criteria. In my example image I'm aiming to remove items in the LIST that start with "Rev". This would greatly reduce the list count and when turned back into text be far more likely to be manageable size.

Would anyone by able to advise how to use something like start with or contains in the context of a list item? Can it be done on the active column, or part of an 'add column' step only? It's easy to see examples of how to use one function but not in the context of the steps/advanced editor and I obviously struggle understanding how these are nested. Not a data analyst by trade so my apologies :(.

[Syndicate_Admin]

As Australians like to say - you're a legend!. This syntax makes perfect sense now. The use of the {} for the list2 part of the List.RemoveMatchingItems clinched it - as was the simple reference to the [Lists including Rev] for list1. 

Thank you so much for the patience to explain this scenario. 

As a mild tangent to reducing steps in PQ - can I ask how to attempt the same (removing items in a list) in the same column rather than adding a new one? Or is that not necessary (because we can add steps to remove)? Thansk again.

[Syndicate_Admin]

Thanks so much for the feedback. Unfortunately this method applies to a column. I wondered if it's possible to utilise the same approach but to a list? Each row in my 'test' column is a List I'd like to 'cleanup' before extracting back to a cell. The technique for using/cleaning a List in this way would be ideal.

[collinsg]

Hi steve-m,

Yes, the same approach may be applied to a list.

In fact, when I use Source[Activity] as the first parameter of List.RemoveMatchingItems, Source[Activity] is a list. In other words referencing a column as table[column] in this context treats the column as a list.

I’ll give an example closer to your requirement. I made this table with a column of lists. Each lists contains items beginning with “Rev”.

Table unexpanded

You can see the lists contain Rev by expanding the column by clicking on the arrows in the column header.

Table expanded

Then, I added a column to the unexpanded table using the code,

    #"Added Custom" = Table.AddColumn(
        Source,
        "Lists excluding Rev",
        each List.RemoveMatchingItems(
                [Lists including Rev],
                {"Rev"},
                each Text.Start(_,3) <> "Rev"
        )
    )

This added a column of lists with items beginning “Rev” removed. This can be seen when you expand the new column.

The full code of the example is

let
    Source = #table(
        {"ID", "Lists including Rev"},
        {
            { "A1", {"Rev A1", "Rev A2", "Bob", "Ann", "Rev 3"} },
            { "B1", {"Peter", "Rev B1", "Rev B2", "George"} }
        }
    ),
    #"Added Custom" = Table.AddColumn(
        Source,
        "Lists excluding Rev",
        each List.RemoveMatchingItems(
                [Lists including Rev],
                {"Rev"},
                each Text.Start(_,3) <> "Rev"
        )
    ),
    #"Expanded Lists excluding Rev" = Table.ExpandListColumn(#"Added Custom", "Lists excluding Rev"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Lists excluding Rev",{"Lists including Rev"})
in
    #"Removed Columns" 

Hope this helps. Unfortunately I'm getting a message "Server too busy" and I'm unable to upload screen shots of the added and expanded column. I'll edit the reply later, if I can, to add these but the full code of the example should be able to show you everything.

[collinsg]

Good day steve-m

The function List.RemoveMatchingItems may help solve this.

1. I created a table in Excel with a column called "Activity". It contained some items starting with "Rev" and others not starting with "Rev". The table was called "Table1".
2. The first parameter of List.RemoveMatchingItems is a list - in this example the "Activity" column.
3. The second parameter is a list of items you want to remove...
4. ...the third parameter of the example says - keep each item not beginning with "Rev".

Here is the M code.

let
   Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Remove Rev Rows" = List.RemoveMatchingItems(
      Source[Activity],
      {"Rev"},
      each Text.Start(_,3) <> "Rev"
   )
in
   #"Remove Rev Rows"

 Here are the results of the "Source" step and the "Remove Rev Rows" step.