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
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
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"
3 Replies
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"