Forum Discussion
how to to split a csv on multiple delimiters in PowerQuery?
I am on this screen:
The data looks like this:
\\asdhf\revi\Tim2,AK\AK_ADMPH,Allow,Fix
\\asdfs1\revi\Tim, John,BUILTIN\Administrators,Allow,None
I want to use “dynamic” delimiters… I want the delimiters to be:
1st delimiter: “,BUILTIN” and “,AK”
Then: the usual comma.
The end result should be:
| \\asdhf\revi\Tim2 | AK\AK_ADMPH | Allow | Fix |
| \\asdfs1\revi\Tim, John | BUILTIN\Administrators | Allow | None |
How can I achieve this? I have tried different ways with no luck
Hi Anonymous ,
How about this:
Before:
After:
It's a two step approach. First, we split the column with your special requirement and afterwards we split the residual column by comma.
Here the code in M:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WigGCxOKUjLSYmKLUssyYmJDMXCMdR++YGEfveEcX3wAPHcecnPxyHbfMCqVYHbiGtGJDJB06Cl75GXk6TqGePiGefkC9KbmZeZnFJUWJJflFxVAT/PLzUpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({",AK",",BUILTIN"}, QuoteStyle.None), {"Column1", "Column2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"}) in #"Split Column by Delimiter1"Let me know, if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
1 Reply
- tackytechtom
Most Valuable Professional
Hi Anonymous ,
How about this:
Before:
After:
It's a two step approach. First, we split the column with your special requirement and afterwards we split the residual column by comma.
Here the code in M:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WigGCxOKUjLSYmKLUssyYmJDMXCMdR++YGEfveEcX3wAPHcecnPxyHbfMCqVYHbiGtGJDJB06Cl75GXk6TqGePiGefkC9KbmZeZnFJUWJJflFxVAT/PLzUpViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({",AK",",BUILTIN"}, QuoteStyle.None), {"Column1", "Column2"}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3"}) in #"Split Column by Delimiter1"Let me know, if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/