topic Re: Extracting complicated text from a column in DAX Commands and Tips

Extracting complicated text from a column

afaherty — Wed, 08 Apr 2026 17:09:30 GMT

Greetings. I am trying to figure out how to extract text from a column which is proving to be more complicated than I anticipated. Here is some of the sample data from that column. I need to extract InTASC (or INTASC) plus the number/letter after.

Current Data	Data Needed
[NAEYC 2010 5a, NAEYC 2020 5a, InTASC 4l]	InTASC 4l
[NAEYC 2020 6a]	(blank)
[NAEYC 2020 6e, InTASC 10j]	InTASC 10j
[InTASC 10m, NAEYC 2020 3d, ISTE 2.4.d.]	InTASC 10m
[ISTE 2.3.a., INTASC 2m]	INTASC 2m
[NAEYC 2020 1b, InTASC 1e, ISTE 2.5.a.]	InTASC 1e

I am open to any method - DAX, M Code, etc. Thank you!

Re: Extracting complicated text from a column

ThxAlot — Wed, 08 Apr 2026 21:46:12 GMT

DAX isn't designed for string manipulation; M doesn't consist of native regex so far. Thus, use embedded python if you're in possession of knowledge of it.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WivZzdI10VjAyMDRQME3UUYBxjSBcz7wQx2BnBZOcWKVYHSTVQGmzRCxiqXAthgZZUHm4QC6K8cYpQLXBIa4KRnomeil6QI4fWJ2ZYTFMI0TWWC8RIWuUi2mrYRLC1lS4oaZAbUC1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current Data" = _t]), #"Run Python script" = Python.Execute("df['Extract'] = df['Current Data'].str.findall(r'intasc .+?\b', 34).str.join(' | ')",[df=Source]), df = #"Run Python script"{[Name="df"]}[Value] in df

In fact, Excel formula is already powerful enough to handle it easily.

Re: Extracting complicated text from a column

lbendlin — Wed, 08 Apr 2026 23:34:26 GMT

Here's a more pedestrian version with "native" Power Query

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WivZzdI10VjAyMDRQME3UUYBxjSBcz7wQx2BnBZOcWKVYHSTVQGmzRCxiqXAthgZZUHm4QC6K8cYpQLXBIa4KRnomeil6QI4fWJ2ZYTFMI0TWWC8RIWuUi2mrYRLC1lS4oaZAbUC1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current Data" = _t]), #"Added Custom" = Table.AddColumn( Source, "Result", each let Start = Text.PositionOf(Text.Upper([Current Data]), "INTASC"), Count = Text.PositionOf(Text.Replace(Text.Range([Current Data], Start), "]", ","), ",") in if Start = - 1 then null else Text.Range([Current Data], Start, Count) ) in #"Added Custom"

Re: Extracting complicated text from a column

AntrikshSharma — Thu, 09 Apr 2026 07:03:15 GMT

afaherty Try this:

let Source = Excel.CurrentWorkbook(){[ Name = "Table1" ]}[Content], Answer = Table.AddColumn ( Source, "Answer", ( x ) => [ a = x[Current Data], b = Text.Remove ( Text.Remove ( a, "[" ), "]" ), c = Text.Split ( b, ", " ), d = List.Select ( c, ( y ) => Text.StartsWith ( Text.Lower ( y ), "intasc" ) ), e = List.First ( d ) ][e], type nullable text ) in Answer

Re: Extracting complicated text from a column

afaherty — Thu, 09 Apr 2026 15:09:28 GMT

lbendlin Thank you. I think something is going awry however. It is pulling in incorrect values into the new custom column (example below). Also, I apologize but I neglected to mention that some rows have more than 1 "InTASC" within them. For example: [InTASC 10m, NAEYC 2020 3d, ISTC 2.4.d., INTASC 61s]

Here is an example of the problem that is now happening which can be seen in the attached file.

Current Data	Custom.Current Data	Custom.Result
[CEC 2020 2.1]	[NAEYC 2010 5a, NAEYC 2020 5a, InTASC 4l]	InTASC 4l
[CEC 2020 2.1]	[NAEYC 2020 6a]
[CEC 2020 2.1]	[NAEYC 2020 6e, InTASC 10j]	InTASC 10j
[CEC 2020 2.1]	[InTASC 10m, NAEYC 2020 3d, ISTE 2.4.d., INTASC 61s]	InTASC 10m
[CEC 2020 2.1]	[ISTE 2.3.a., INTASC 2m]	INTASC 2m
[CEC 2020 2.1]	[NAEYC 2020 1b, InTASC 1e, ISTE 2.5.a.]	InTASC 1e

Thanks again!

Re: Extracting complicated text from a column

lbendlin — Thu, 09 Apr 2026 18:24:45 GMT

What is your expected outcome in such situations?

Re: Extracting complicated text from a column

v-karpurapud — Mon, 13 Apr 2026 10:22:32 GMT

Hi afaherty
Thank you for reaching out to the Microsoft Fabric community forum.

You can do this natively in Power Query without Python by splitting the list into tokens, trimming, then filtering only the items that start with INTASC/InTASC (case-insensitive). This also handles rows with multiple INTASC values and returns them joined with |.

Add a custom column with:

each let t = [Current Data], cleaned0 = if t = null or Text.Trim(t) = "" then null else t, cleaned = if cleaned0 = null then null else Text.Replace( Text.Replace( Text.Replace( Text.Replace(cleaned0, "[", ""), "]", ""), "(", ""), ")", ""), parts = if cleaned = null then {} else List.Transform( Splitter.SplitTextByAnyDelimiter({",", ";"}, QuoteStyle.Csv)(cleaned), each Text.Trim(_) ), intascOnly = List.Select( parts, each Text.StartsWith(Text.Trim(_), "INTASC", Comparer.OrdinalIgnoreCase) ), result = if List.Count(intascOnly) = 0 then null else Text.Combine(intascOnly, " | ") in result

This will return InTASC 10m | INTASC 61s when multiple values exist, or blank/null if none are present.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Regards,

Microsoft Fabric Community Support Team

Re: Extracting complicated text from a column

afaherty — Mon, 13 Apr 2026 15:51:02 GMT

This is fantastic, thank you!!

Re: Extracting complicated text from a column

afaherty — Mon, 27 Apr 2026 16:11:37 GMT

Hi v-karpurapud ,

I'm afraid I've run into an issue. The formula you so kindly provided is skipping the first InTASC value if there's no comma before it. For example:

Text in column	Your formula is extracting	Should extract	Issue
[InTASC 5b, AMLE 3m]	null	InTASC 5b	Isn't pulling the InTASC because there's no comma before it
[AMLE 1b, InTASC 2g]	InTASC 2g	InTASC 2g	No issue, working fine because there is only an InTASC after a comma
[InTASC 3n, InTASC 8c]	InTASC 8c	InTASC 3n \| InTASC 8c	Isn't pulling the first InTASC because there's no comma before it

Any thoughts? Thank you!!

Re: Extracting complicated text from a column

v-karpurapud — Tue, 28 Apr 2026 05:07:41 GMT

Hi afaherty

The issue is caused by hidden or non-printable characters in the first token. When the string starts directly with InTASC, those characters can prevent Text.StartsWith from matching correctly, even after trimming. That’s why the first value is being skipped, while subsequent values (after commas) work fine.

Applying Text.Clean and normalizing the text before filtering resolves this.

Add a custom column with:

each let t = [Current Data], cleaned0 = if t = null or Text.Trim(t) = "" then null else t, cleaned = if cleaned0 = null then null else Text.Replace( Text.Replace( Text.Replace( Text.Replace(cleaned0, "[", ""), "]", ""), "(", ""), ")", ""), parts = if cleaned = null then {} else List.Transform( Splitter.SplitTextByAnyDelimiter({",", ";"}, QuoteStyle.Csv)(cleaned), each Text.Upper(Text.Trim(Text.Clean(_))) ), intascOnly = List.Select( parts, each Text.StartsWith(_, "INTASC") ), result = if List.Count(intascOnly) = 0 then null else Text.Combine(intascOnly, " | ") in result

This ensures the first InTASC value is correctly detected and also continues to handle multiple matches as expected.

I hope this clarifies things. Let me know what you find after trying these steps. We will assist you further.

Regards,

Microsoft Fabric Community Support Team.

Re: Extracting complicated text from a column

afaherty — Fri, 01 May 2026 16:08:46 GMT

Hi! Thank you v-karpurapud ! So something I failed to mention is that there are words prior to every starting bracket [ in the cell

So, each cell looks something like: Words words words [InTASC 3n, InTASC 8c]

And so from "Words words words [InTASC 3n, InTASC 8c]" I am trying to pull out InTASC 3n | InTasc 8c

Re: Extracting complicated text from a column

v-karpurapud — Mon, 04 May 2026 10:35:02 GMT

Hi afaherty

Since the text can contain words before the brackets, the correct approach is to first extract only the content inside [ ... ], then split and filter for InTASC.

Add a custom column with.

each let t = [Current Data], insideBrackets = if t = null then null else try Text.BetweenDelimiters(t, "[", "]") otherwise null, parts = if insideBrackets = null then {} else List.Transform( Splitter.SplitTextByAnyDelimiter({",", ";"}, QuoteStyle.Csv)(insideBrackets), each Text.Trim(Text.Clean(_)) ), intascOnly = List.Select( parts, each Text.StartsWith(_, "INTASC", Comparer.OrdinalIgnoreCase) ), result = if List.Count(intascOnly) = 0 then null else Text.Combine(intascOnly, " | ") in result

This will correctly return InTASC 3n | InTASC 8c for multiple matches, InTASC 5b for a single match, or null if no match is found.

If you have any other questions, feel free to contact us. We're here to help.

Regards,

Microsoft Fabric Community Support Team.

Re: Extracting complicated text from a column

afaherty — Mon, 04 May 2026 17:02:04 GMT

Amazing!! Thank you!!