Forum Discussion
M Function to split dynamic delimeter
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 ...
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"
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"- 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?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"