Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Fabric Community site will be in read-only mode on Monday, Feb 24 from 12:01 AM to 8 AM PST for scheduled upgrades.

Reply
m00ch
Regular Visitor

Extracting a number string after a two character delimiter

Hello everyone, 

Could someone help me create an efficient way to extract an alphanumeric string that varies in length but always comes after the first "T#"? There may be a whole bunch of garbage before the delimiter like dates and times as well as after. There can also be a Carriage Return (CR) immediately after the string. Here is what the data looks like showing all characters and the string that I would like to extract is highlighted.

 

m00ch_1-1738872250691.png

 

2/6/2022 3:05:16 PM: Audit: T#23A7561845685567
2/5/2024 1:57:54 PM: Audit: Following up for update.
2/3/2025 6:20:11 PM: Audit: Requested update.
1/24/2025 3:44:07 PM: Audit: Date=1/2/2015

 

 

12 REPLIES 12
m00ch
Regular Visitor

I have tried the solutions above and the issue is that the solutions above would work great if there was no data after the CR and LF's. Unfortunately there is data after the CR and LF's as you can see in the example below. I have also tried to split columns using CR as the split point and then using textbetweendelimiters and although this works most of the time it seems to be a quite messy solution.

 

Below in Column1 is the data and Column2 I have what I want to pull out of the data in Column1. 

m00ch_0-1739800392186.png

 

Hi @m00ch 

To better understand the issue and provide an accurate solution, could you share a sample dataset in a .csv or Excel file? This will allow us to analyze the data structure and understand how the CR and LF characters are impacting the results. Please anonymize any sensitive information if necessary before sharing. 

Thank you!

v-karpurapud
Community Support
Community Support

Hello @m00ch 

Could you please confirm if your query have been resolved the solution provided by @dufoq3 ?If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

Fowmy
Super User
Super User

@m00ch 

You may try AddColumn > Extract > Text Between Delimiters:

= Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([Column1], "T#", " "), type text)



Fowmy_0-1738874010013.png

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

m00ch
Regular Visitor

I am sorry, I did not describe that well. The screenshot is the data that is all in one row with Carriage Returns (CR). So using a space as the second delimiter does not seem to work well and if I use clean it removes CR but puts the data of the next line right up against the alphanumeric string that I am trying to get. Which essentially eliminates the second delimiter.  

Hi @m00ch

 

if this code doesn't work with your data. Replace "#(lf)" with "#(cr)"

 

Output

dufoq3_0-1738879309240.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/LCsIwEIXhVxnqVmxmkkllwEVB3Aki7touhEYpaOslRR/fqReocCCb/4NJUSSU+pQMEVgxLOhhsxbI+7qJArsJ2Txjj3PHfs7ss7JNqumAeEAOUDgTdmO06k6n7tG0R+gvcOhu+tT7GGZfaAfI4IWMII7hNlz7cI+h/hOYkvsQK86JycZkqdlCCw2Q37nBVEfGGL0Nh0H+6/U/SLlF0jld2d67c4AYnjGpqhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_Extracted = Table.AddColumn(Source, "Extracted", each Text.BetweenDelimiters([Column1], "T#", "#(lf)"))
in
    Ad_Extracted

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

HI @dufoq3  and @Fowmy  Unfortunately that does not work either, the CF and LB's are in the same cell. Hopefully this screenshot will explain it more clearly.

 

m00ch_0-1739177832465.png

 

 

 

Hi @m00ch, so it works. You wanted to extract "extract an alphanumeric string that varies in length but always comes after the first "T#""


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@dufoq3 I do appreciate your help and it seems that I wasn't clear enough. I should have said or displayed better that the CR and LF was in the same cell and that there was more data after the CR and LF. That is my fault. My initial screenshot was from one row in Power Query but I can see how it looks like separate rows. 

 

I'll do better next time.

Hi @m00ch 
Hope you are doing well!

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Unfortunately no, it did not resolve my issue as I did not clearly state my issue in the first post.

Hello @m00ch 

Thank you for your response. To assist you better, could you provide more details about the issue? Specifically, please share the exact issue you're facing, any error messages you have encountered, and the steps you have taken so far. This information will help us to provide  more accurate and effective solution. Looking forward to your clarification.

Thank you!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors