Extract text between every occurrence of delimeters

[Silvard]

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 

[Anonymous]

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

[Silvard · Accepted solution]

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, "")

)

[ThxAlot]

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

[KNP]

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

[Silvard]

Thanks heaps @KNP 

I've never had to do this before.

Where do I input the execution please?

[KNP]

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.

[Silvard]

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?

[KNP]

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.

[Silvard]

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?

[KNP]

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

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

[FreemanZ]

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

[Silvard]

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