Forum Discussion

Resolver I

6 years ago

Solved

Split text based on every second delimiter

Hi, looking for assistance with manipulating columns in Power Query.

I need to split a column based on a Comma delimiter, however I need to only split every second one. The data is formatted Surname, Initials with a Comma delimiter between people. There are multiple people in each row (can be between 1 and 1,000 values in each row). I am not looking to split into columns as part of this process.

I want to extract into a list, but have each Surname, Initials combination, not Surname then Initials on the next row.

Example Data

Person

Chapman, D.A., Baker, R., Thomas, B.L.

Davis, Q., Grant, M.R.

Desired Result

Person

Chapman, D.A.

Baker, R.

Thomas, B.L.

Grant, M.R.

Davis, Q.

Have been trying the code from here: https://community.powerbi.com/t5/Desktop/Text-Replace-specific-character-only-if-it-occurs-as-the-last/td-p/291553,

Table.TransformColumns(#"Changed Type",{{"Column1", each Text.Split(_," ")}})

Hope that explains what I am after.

Cheers

Phil

Need Help

az38

6 years ago

Hi PhilC

try split by ".,"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5ILMhNzNNRcNFz1NNRcErMTi3SUQgCMkMy8nMTi4FCej56SrE60UouiWWZQH4gUM69KDGvREfBVy8IKBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Person", Splitter.SplitTextByDelimiter(".,", QuoteStyle.Csv), {"Person.1", "Person.2", "Person.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Person.1", type text}, {"Person.2", type text}, {"Person.3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each [Value]&"."),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","..",".",Replacer.ReplaceText,{"Custom"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Custom"})
in
    #"Removed Other Columns"

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

Jimmy801

6 years ago

Hello PhilC

had created once a nice function that can be used here. Soltuion by az38 is nice, but would work only in for this 3 cells. This function can be applied dynamically

(tSplitText as text, 
tDelimiter as text, 
nOccurance as nullable number) as list =>

let
    nOccuranceIntern = if nOccurance = null then 2 else nOccurance,
    Source = "Chapman, D.A., Baker, R., Thomas, B.L.",
    ListFromText = Text.ToList(tSplitText),
    CreateListOfDelimiter = List.Accumulate
        (
            ListFromText,
            [CurrentRow = -1, DelimiterFound={}],
            (state, current)=>
                if current = tDelimiter then
                        Record.TransformFields(state, {{"DelimiterFound", each _ & {state[CurrentRow]}},{"CurrentRow", each _ +1 }})
                        else
                        Record.TransformFields(state, {{"CurrentRow", each _ +1 }})
        )[DelimiterFound],
    ListAlternate = List.Alternate(CreateListOfDelimiter,nOccuranceIntern-1,nOccuranceIntern-1),
    ChangeTextList = List.Accumulate
        (
            ListAlternate,
            ListFromText,
            (state, current)=>
                List.ReplaceRange(state,current+1,1,{"%%%"})
        ),
    SplitTextByNewDelimiter = Text.Split
        (
            List.Accumulate
                (
                    ChangeTextList,
                    "",
                    (state, current)=> 
                        state & current
                ),
            "%%%"
         )


in
    SplitTextByNewDelimiter

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Mariusz

6 years ago

Hi PhilC

a third option for you to choose.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5ILMhNzNNRcNFz1NNRcErMTi3SUQgCMkMy8nMTi4FCej56SrE60UouiWWZQH4gUM69KDGvREfBVy8IKBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,".,",".#",Replacer.ReplaceText,{"Person"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Person", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Person"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Person", Text.Trim, type text}})
in
    #"Trimmed Text"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

10 Replies

az38

Community Champion

6 years ago

Hi PhilC

try split by ".,"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5ILMhNzNNRcNFz1NNRcErMTi3SUQgCMkMy8nMTi4FCej56SrE60UouiWWZQH4gUM69KDGvREfBVy8IKBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Person", Splitter.SplitTextByDelimiter(".,", QuoteStyle.Csv), {"Person.1", "Person.2", "Person.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Person.1", type text}, {"Person.2", type text}, {"Person.3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each [Value]&"."),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","..",".",Replacer.ReplaceText,{"Custom"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Custom"})
in
    #"Removed Other Columns"

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

PhilC
Resolver I
6 years ago
Hi az38

Thanks for that, the suggestion for using ".," is what I needed, no idea why I did not see that. Thank you.

Edit: just realised that it means the "." will be dropped when splitting, which is not ideal.

Regarding the code, I need an approach that does not split to columns due to the size of the contents (up to 1,000) - this was mentioned in the post, but did not mention that I have three other fields that also need to be expanded, so need to use Lists in the columns.
Cheers, Phil
- Jimmy801
  Community Champion
  6 years ago
  Hello PhilC
  
  that is the downside using this "simply" splitting. You have to remaintain data afterwards. And if you applying the logic to a larger dataset, the chances are quite big, this "simplier" solutions won't work (more than 3 parts, ".," not present, but only ","
  So "simply" use my function and you are at the save side 🙂
  
  If this post helps or solves your problem, please mark it as solution.
  Kudos are nice to - thanks
  Have fun
  
  Jimmy

Jimmy801

Community Champion

6 years ago

Hello PhilC

had created once a nice function that can be used here. Soltuion by az38 is nice, but would work only in for this 3 cells. This function can be applied dynamically

(tSplitText as text, 
tDelimiter as text, 
nOccurance as nullable number) as list =>

let
    nOccuranceIntern = if nOccurance = null then 2 else nOccurance,
    Source = "Chapman, D.A., Baker, R., Thomas, B.L.",
    ListFromText = Text.ToList(tSplitText),
    CreateListOfDelimiter = List.Accumulate
        (
            ListFromText,
            [CurrentRow = -1, DelimiterFound={}],
            (state, current)=>
                if current = tDelimiter then
                        Record.TransformFields(state, {{"DelimiterFound", each _ & {state[CurrentRow]}},{"CurrentRow", each _ +1 }})
                        else
                        Record.TransformFields(state, {{"CurrentRow", each _ +1 }})
        )[DelimiterFound],
    ListAlternate = List.Alternate(CreateListOfDelimiter,nOccuranceIntern-1,nOccuranceIntern-1),
    ChangeTextList = List.Accumulate
        (
            ListAlternate,
            ListFromText,
            (state, current)=>
                List.ReplaceRange(state,current+1,1,{"%%%"})
        ),
    SplitTextByNewDelimiter = Text.Split
        (
            List.Accumulate
                (
                    ChangeTextList,
                    "",
                    (state, current)=> 
                        state & current
                ),
            "%%%"
         )


in
    SplitTextByNewDelimiter

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

PhilC
Resolver I
6 years ago
Hi Jimmy801 , the function works, thanks. I think splitting by ".," / ".#" is a simpler solution so will go with that one.
gauravsukhadia
Frequent Visitor
3 years ago
Hi Jimmy801 above function is excellent and I have a similar problem where I want to get result in the group of 10 values ( trying to pass 10 containers to the string including '&' symbol except in the 10th posiiton which is api call to get the status) and then split into rows until the last. Could you please advise where do i need to modify value in this function ? Hi Team above function is excellent and I have a similar problem where I want to get result in the group of 10 values and then split into rows. Could you please advise where do i need to modify value in this function ?

Mariusz

Community Champion

6 years ago

Hi PhilC

a third option for you to choose.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5ILMhNzNNRcNFz1NNRcErMTi3SUQgCMkMy8nMTi4FCej56SrE60UouiWWZQH4gUM69KDGvREfBVy8IKBULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,".,",".#",Replacer.ReplaceText,{"Person"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Person", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Person"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Person", Text.Trim, type text}})
in
    #"Trimmed Text"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

PhilC
Resolver I
6 years ago
Hi Mariusz , that is a good approach, keeps the "." portion of the delimiter, and results in the List that I am after.

Thanks.

Forum Discussion

Split text based on every second delimiter

10 Replies

Related content

split column by delimiter in dax

DAX formula help splitting string using delimiters

Splitting multiple columns using delimiter

split by delimiter

How to split strings by a delimiter?