Скачать или смотреть How to Remove Items from a List Matching a Pattern in PowerQuery

Learn how to efficiently remove unwanted items from lists in PowerQuery by using the right functions and eliminating pattern matches effortlessly.
---
This video is based on the question https://stackoverflow.com/q/65515487/ asked by the user 'sayth' ( https://stackoverflow.com/u/461887/ ) and on the answer https://stackoverflow.com/a/65515918/ provided by the user 'Ron Rosenfeld' ( https://stackoverflow.com/u/2872922/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PowerQuery remove items from a list matching a pattern

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Removing Items from a List Matching a Pattern in PowerQuery

If you've ever worked with PowerQuery in Excel, you may have found yourself facing a common issue: needing to remove items from a list that match a specific pattern. This challenge often arises when dealing with data like names and identifiers, such as in the example below:

Example Data:

[[See Video to Reveal this Text or Code Snippet]]

In this case, each element in the list represents a name followed by some unique numeric identifier prefixed by # . Your goal is to strip out the identifiers and keep only the names. Let’s explore how to achieve this using PowerQuery.

Understanding the Problem

The primary challenge is to identify and remove elements that match a specific pattern (in this case, numeric identifiers) while keeping the relevant data (the names).

Your Initial Approach

You initially tried to split your data using a separator, which is a good first step:

[[See Video to Reveal this Text or Code Snippet]]

However, you also wanted to explore options for removing every second item or those that match the pattern defined by regex.

Solution Steps

Step 1: Splitting the Text

First, split the text into a list as you did:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Selecting Desired Items

Using the List.Select function, you can select even-numbered items from your list. This will help in retaining the names while excluding the numeric patterns:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: An Alternative Method

Another efficient method to remove alternate elements is by using:

[[See Video to Reveal this Text or Code Snippet]]

This function allows you to specify how to alternate through the list without the need of explicitly matching various numeric patterns.

Conclusion

In summary, both methods effectively allow you to filter out unwanted items from your list in PowerQuery. By splitting the text and selecting or alternating through the items, you can easily manage and clean your data.

Tips for Future Use

Experiment with both List.Select and List.Alternate to understand their functionalities better.

Familiarize yourself with other list functions available in PowerQuery to broaden your data manipulation skills.

By following the steps highlighted in this guide, you can efficiently remove items that do not fit your desired pattern, keeping your data clean and manageable.

If you have more questions about PowerQuery or need further assistance, feel free to reach out!