Split Row by X instance of a delimiter power query

user_13562186 0
Viewed 153

I have the following data which I wish to split as follows:

enter image description here

The condition here is that it splits on the first instance of @ provided 2 or more are present in the row.

I would like this to be dynamic, so I can specify which instance @ I would like the split to occur on.

So far all I have been able to do is the count the number of @ found and if greater than 1, say "test".

I could just split using the inbuilt split by delmiter and select the first instance but Its actually interesting to know how to do this for say the 4th instance. This doesnt appear to be an option. You can use Extract data after delimiter and specify there, but wondering if there is a more straigh forward solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Count(Text.PositionOf([Column1],"@", Occurrence.All)) > 1 then "test" else null)
in
    #"Added Custom"

Data:

Apple Pear @ bananna
Apple cherry orange @ lime pinapple @ lemon 
Kiwi @ banana
Orange @ lime @
Strawberry @ Blueberry @
powerbi powerquery excel
3 Answers

How about something along these lines

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1],"@")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Before", each Text.Combine(List.FirstN([Custom],[Column2])," ")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "After", each Text.Combine(List.LastN([Custom],List.Count([Custom])-[Column2])," ")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.1", each {[Before],[After]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Column1", "Column2", "Custom", "Before", "After"}),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1")
in #"Expanded Custom.1"
Column1 Column2
a @ f @ g @ h 1
a @ f @ g @ h 2
a @ f @ g @ h 3
a @ f @ g @ h 4
a @ f @ g @ h 1
a @ f @ g @ h 2
a @ f @ g @ h 3
a @ f @ g @ h 4

enter image description here

You'd have to add a few Text.Trims() and List.Transforms()to addback the @s where desired

user_92642300 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.Count(Text.PositionOf([Column1],"@", Occurrence.All)) > 1 then [Column1] else null),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Custom", Splitter.SplitTextByEachDelimiter({"@"}, QuoteStyle.None, false), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom] = null then [Column1] else [Custom]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"})
in
    #"Removed Other Columns"
user_135621860

Another rather simple idea; change the nth occurence of @ in another character that does not occur in your data, in my sample below it's the |:

try Text.ReplaceRange([Column1], Text.PositionOf([Column1], "@", 100){n-1}, 1, "|") otherwise [Column1]

The 100 is meant as a placeholder to whichever maximum amount of @ you'd expect. You can get the Text.Length() function involved but I went with a constant. Also, where {n-1} is the part that will dynamically change whichever occurence of the @ you'd want to change. For splitting on the 2nd occurence, you'd need {2-1}. When done, you can then do your split into rows.

EDIT: I made a custom function for the above with variables for you to be dynamic:

(Input as text, Delimiter as text, Replacement as text, N as number) as text =>
let
    Output = try Text.ReplaceRange(Input, Text.PositionOf(Input, Delimiter, 100){N-1}, 1, Replacement) otherwise Input
in
    Output

When we invoke this custom function it asks you for the parameters:

![enter image description here

Resulting in:

enter image description here

It's now a simple step to split the data into rows on the given replacement character. You may also want to trim these values.

user_97581940
Related