Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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"
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"
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |