Search code examples
excelpowerquerym

PowerQuery remove items from a list matching a pattern

Using PowerQuery in excel how can I remove items from a list that match a pattern.

I have a column with cells that contain names and numeric id's. I want to be left with just a list of names.

LastName, FirstName;#123;#LastName, FirstName;#321;

The numbers are all unique. So if I had regex the pattern would be similar to

/^\#ddd+$/

I can split the cell into a list using ';' as a separator.

= Text.Split([Consultant],";")

If there was a way to remove every 2nd item until the end that could work too. Unfortunately it seems there is no way to specify patterns to match.

List.RemoveItems({1, 2, 3, 4, 2, 5, 5}, {2, 4, 6})

This would be awesome however I have to define all the number patterns that exist. So this fails.

List.RemoveMatchingItems(Text.Split([Consultant], ";#"), {1,2,3,4,5,6,7,8,9})

Method2 I split the text into a list as above. This gave me a column of lists. So I expanded the lists in columns to new rows. My plan was to remove alternate rows. However, remove alternate rows requires an end number. I would need an argument to go until there are no more arguments to process.

Solution

  • There are many ways. One way is to select every other item with List.Select

    In your example, these would be the items with an even number position.

    let 
  x = Text.Split([Column1],";#"),
  y = List.Select(x, each Number.IsEven(List.PositionOf(x, _)))
in 
 y

    Edit Nov 2022
    Another method or removing every other would be:

    =List.Alternate(Text.Split([Column1],";#"),1,1,1)