Скачать или смотреть How to Remove Text Strings Containing Lower Case Letters in Power Query

Discover how to efficiently filter out unwanted text strings and numbers in Power Query to retain only upper case text. A step-by-step guide for beginners!
---
This video is based on the question https://stackoverflow.com/q/69486540/ asked by the user 'Qqqqq' ( https://stackoverflow.com/u/6726313/ ) and on the answer https://stackoverflow.com/a/69489431/ 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: Power Query - Remove text strings that contain lower case letters

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.
---
How to Remove Text Strings Containing Lower Case Letters in Power Query

When working with Power Query, you might encounter situations where your data is not formatted correctly, especially when importing from sources like PDF files. This can lead to messy columns that contain a mix of numbers, upper case, and lower case text strings.

If you wish to keep your dataset clean, one common goal is to filter out all numbers and any mixed-case text strings, resulting in a list that only includes fully upper case text strings. In this guide, we'll go through a detailed solution on how to achieve this.

Understanding the Problem

Let's say you have a column where:

Desired outputs: IRA, IRREVOCABLE TRUST, CHARITABLE TRUST

Unwanted outputs: Number of Accounts, Totals, 14

You want to filter out all instances that are either numbers, mixed-case words, or contain lower case letters.

Step-by-Step Solution

Here are methods you can use to tackle this issue, whether your dataset contains multiple strings in a single cell or just one string per cell:

Method 1: Handling Multiple Text Strings in a Cell

If your data comprises multiple space-separated strings in a single cell, follow this approach:

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

Method 2: Handling a Single Text String in a Cell

If your dataset has a single string in each cell, you can use this method:

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

Final Steps

After you have added your custom column:

Filter the Column: You can easily filter out the null values in the added column to keep only the desired upper-case strings.

Adjust Previous Step Name: Ensure that -"Changed Type" matches the name of your previous step in Power Query; adjust it accordingly if it differs in your query.

Conclusion

By following these methods, you can efficiently clean your Power Query dataset to remove all unwanted numbers and mixed-case text strings. The result will leave you with a neat list of fully upper case text, perfect for further analysis or reporting.

Feel free to reach out if you have any questions or need further assistance with Power Query. Happy querying!