topic Re: Extract text between every occurrence of delimeters in DAX Commands and Tips

Extract text between every occurrence of delimeters

Silvard — Fri, 01 Nov 2024 01:01:24 GMT

I have a Location column with suburb and state.

Example:

Sydney - NSW, Melbourne - VIC, Canberra - ACT.

how would I create a new column, either calculated or in power query that extracts all the states with comma as separator?

Desired output, ideally alphabetically ordered:

ACT, NSW, VIC

Re: Extract text between every occurrence of delimeters

FreemanZ — Fri, 01 Nov 2024 01:47:55 GMT

hi Silvard ,

this is standard operation for Power Query, just give it a try and you will like it.

About how:

https://learn.microsoft.com/en-us/power-query/split-columns-delimiter

Re: Extract text between every occurrence of delimeters

KNP — Fri, 01 Nov 2024 02:52:57 GMT

Hi Silvard ,

It's not quite out of the box functionality.

This function (Paste into a blank query in the advanced editor) call it fSplitCombineOrder:

let SplitAndSortStates = (inputTable as table, columnName as text) as table => let SplitColumn = Table.ExpandListColumn( Table.TransformColumns( inputTable, {{columnName, Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), type text}} ), columnName ), ExtractState = Table.TransformColumns( SplitColumn, {{columnName, each Text.AfterDelimiter(_, " - "), type text}} ), RemoveDuplicates = Table.Distinct( ExtractState ), SortStates = Table.Sort( RemoveDuplicates, {{columnName, Order.Ascending}} ), CombineStates = Table.AddColumn( SortStates, "Combined", each Text.Combine(List.Sort(List.Distinct(Table.Column(SortStates, columnName))), ", "), type text ), Result = Table.Distinct( Table.SelectColumns(CombineStates, {"Combined"}) ) in Result in SplitAndSortStates

Example execution:

let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45WCq5MyUutVNBV8AsO11HwTc1Jyi8tyksFCoR5OusoOCfmJaUWFSUC+Y7OIUqxOsNERywA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t] ), AddedCustom = Table.AddColumn( Source, "fSplitCombineOrder", each fSplitCombineOrder(Source, "Column1") ), ExpandedCustom = Table.ExpandTableColumn( AddedCustom, "fSplitCombineOrder", {"Combined"}, {"Combined"} ) in ExpandedCustom

Re: Extract text between every occurrence of delimeters

Silvard — Fri, 01 Nov 2024 02:57:05 GMT

Hi Freeman,

I'm aware of this feature but I end up with a merged column with multiple commas repeated and some states are duplicated in other rows.

I would ideally prefer a simple DAX or M query

Re: Extract text between every occurrence of delimeters

Silvard — Fri, 01 Nov 2024 03:12:03 GMT

Thanks heaps KNP

I've never had to do this before.

Where do I input the execution please?

Re: Extract text between every occurrence of delimeters

KNP — Fri, 01 Nov 2024 03:21:24 GMT

In Power Query, right click in the queries pane in a blank space and choose blank query.

Right click on your new query and select Advanced editor...

Replace the code with the function code I've provided and click done.

Rename the query... (you can call it whatever you like but that's how you'll reference it)

In your table, you'll create a step like...

= Table.AddColumn( Source, "fSplitCombineOrder", each fSplitCombineOrder(Source, "Column1") )

Change 'Source' and '"Column1"' to whatever your previous step and column is called. You need the "" around the column name.

The final step is to expand the column to get the output...

= Table.ExpandTableColumn( AddedCustom, "fSplitCombineOrder", {"Combined"}, {"Combined"} )

I hope that helps but feel free to ask any questions on the specific steps.

Re: Extract text between every occurrence of delimeters

Silvard — Fri, 01 Nov 2024 04:12:25 GMT

It certainly helps and I appreciate your assistance.

I get an invoked function and a query, but your screenshot only shows the query? Which am I using?

can you please also expand on Change 'Source' and '"Column1"' to whatever your previous step and column is called?

Re: Extract text between every occurrence of delimeters

KNP — Fri, 01 Nov 2024 05:59:23 GMT

So in my prior reply, I pasted code for Example execution...

You can copy that code, as it is, into a new blank query. Same as you did with the function.

This query is just an example of the function envoked.

The AddCustom and ExpandedCustom steps are what you want to add to your table.

I included that so you could see how it functions.

When you add these steps, you will need to change 'Source' to whatever the previous step in your table is.

Re: Extract text between every occurrence of delimeters

Silvard — Fri, 01 Nov 2024 09:54:27 GMT

When I add the the first step, I get Table in all rows. Highlighting a row here, I can see Error in all rows under fSplitCombineOrder.

Any idea what's wrong?

Re: Extract text between every occurrence of delimeters

KNP — Fri, 01 Nov 2024 17:11:02 GMT

Can you paste your code? (just change anything sensitive).

It's a bit hard to debug without seeing it.

Re: Extract text between every occurrence of delimeters

ThxAlot — Fri, 01 Nov 2024 21:31:50 GMT

If you could turn to a colleague who is really proficient in both Power Query and Python, he/she might help you implement this simple solution.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCq5MyUutVNBV8AsO11HwTc1Jyi8tyksFCoR5OusoOCfmJaUWFSUC+Y7OIXpKsTokatFRiKgAAiDH2ckRqD8WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]), #"Run Python script" = Python.Execute("import re#(lf)import pandas as pd#(lf)#(lf)def extract(string):#(lf) matches = re.finditer(r'(?<= )\w+(?=[,.])', string, re.I)#(lf) return ','.join([m.group() for m in matches])#(lf)#(lf)dataset['Extract'] = dataset['String'].apply(extract)",[dataset=Source]), dataset = #"Run Python script"{[Name="dataset"]}[Value] in dataset

Re: Extract text between every occurrence of delimeters

Anonymous — Tue, 26 Nov 2024 08:47:38 GMT

Hi Silvard ,

How is the situation now? If the problem has been solved, please accept replies you find helpful as solutions.

Here is another method about calculated column for your reference, but the disadvantage of this method is that it cannot be sorted alphabetically.

ConcatenatedCharacters = VAR CommaPositions = ADDCOLUMNS( GENERATESERIES(1, LEN('Table'[Location])), "Position", FIND("-", 'Table'[Location], [Value], LEN('Table'[Location])) ) VAR DISPOST = DISTINCT(SELECTCOLUMNS(CommaPositions,[Position])) VAR Characters = CONCATENATEX( ADDCOLUMNS( DISPOST, "CharAfterComma", MID('Table'[Location], [Position] + 1, 4) ), [CharAfterComma], "," ) RETURN Characters

Best regards,

Mengmeng Li

Re: Extract text between every occurrence of delimeters

Silvard — Tue, 26 Nov 2024 13:37:01 GMT

Hi Anonymous

thanks for your input.

I have solved it using the below DAX structure.

Extract =

VAR InputText = 'YourTable'[YourColumn]

VAR FirstDashPos = FIND(" - ", InputText, 1, 0)

VAR FirstCommaPos = FIND(", ", InputText, FirstDashPos + 3, 0)

VAR FirstWord = IF(

AND(FirstDashPos > 0, FirstCommaPos > FirstDashPos),

MID(InputText, FirstDashPos + 3, FirstCommaPos - FirstDashPos - 3),

BLANK()

)

VAR SecondDashPos = IF(FirstCommaPos > 0, FIND(" - ", InputText, FirstCommaPos + 2, 0), 0)

VAR SecondCommaPos = IF(SecondDashPos > 0, FIND(", ", InputText, SecondDashPos + 3, 0), 0)

VAR SecondWord = IF(

AND(SecondDashPos > 0, SecondCommaPos > SecondDashPos),

MID(InputText, SecondDashPos + 3, SecondCommaPos - SecondDashPos - 3),

BLANK()

)

VAR ThirdDashPos = IF(SecondCommaPos > 0, FIND(" - ", InputText, SecondCommaPos + 2, 0), 0)

VAR ThirdCommaPos = IF(ThirdDashPos > 0, FIND(", ", InputText & ",", ThirdDashPos + 3, 0), 0)

VAR ThirdWord = IF(

AND(ThirdDashPos > 0, ThirdCommaPos > ThirdDashPos),

MID(InputText, ThirdDashPos + 3, ThirdCommaPos - ThirdDashPos - 3),

BLANK()

)

RETURN

CONCATENATE(

CONCATENATE(FirstWord, IF(NOT(ISBLANK(SecondWord)), ", " & SecondWord, "")),

IF(NOT(ISBLANK(ThirdWord)), ", " & ThirdWord, "")

)