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

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 @

CodePudding user response：

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"

CodePudding user response：

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"

You'd have to a few Text.Trims() and whatnot