M Function to split dynamic delimeter

[Anonymous]

Hi there

I got a situation where i need to be able to split a colum (long string -> product description) into 2 columns. However the delimeter will be determined by another column. For example

Product Description                                                  Product Code

Iphone S4, A1-0315-0123                                         A1

Training 01/04/2016, S1-xxx-xxx 1 attendee            S1

THe reason I cant go with comma delimeter or space or any other type is because of bad data input, the Product Code can happen at the beginning, the end of the middle of Product Description. However the combination is always the same that goes by ProductCode-mmyy-XXXX.

Hope you can advice.

Pedzilla

[MarcelBeug · Accepted solution]

The code below inserts a delimiter before the productcode, i.e. it searches for the productocde followed by "-" (e.g. "A1-").

This part is coded via the advanced editor.

After that, the column is splitted using standard menu options.

let
    Source = Table1,
    #"Replaced Value" = Table.ReplaceValue(Source,each [Product Code]&"-",each "ThisIsNowTheDelimiter"&[Product Code]&"-",Replacer.ReplaceText,{"Product Description"}),
    RestoredColumnTypes = Value.ReplaceType(#"Replaced Value",Value.Type(Source)),
    #"Split Column by Delimiter" = Table.SplitColumn(RestoredColumnTypes, "Product Description", Splitter.SplitTextByEachDelimiter({"ThisIsNowTheDelimiter"}, QuoteStyle.Csv, false), {"Product Description.1", "Product Description.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Product Description.1", type text}, {"Product Description.2", type text}})
in
    #"Changed Type"

[Anonymous]

Hi there
Thank you for your repl @MarcelBeug
Problem is my product description might have more than one "-" occasionally different operator put that in for example "training - powerbi day 1, tr-0212-0123"
Do you think this code can cope with this irregularity?

[MarcelBeug]

Yes, it can.

There will only be a problem if the combination of the code with the "-" would appear multiple times, e.g.

"tr- powerbi day 1, tr-0212-0123"