Forum Discussion
Split *multiple* columns by non-digit to digit
Hello,
Using the menus, I can split a column into two, which creates M code something like below if my column is named Column1
#"Split Column by Character Transition" = Table.SplitColumn(#"Capitalized Each Word", "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"})
But how can I do this for multiple columns at once?
It seems if I select all the columns that I want to transform, the Split Columns button is greyed-out
(Cross posted here: https://www.mrexcel.com/board/threads/split-multiple-columns-by-non-digit-to-digit.1202256)
6 Replies
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcvBDcAgDEPRXXLmVhzSO3KRMwJi/zVKqeTb85/ToiEgaoi0Yo6oXd1WmebhEU7+gv0kn+SxiusSUzpNgwbzAJozubehOfz+smFrvQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna1 = _t, Colonna2 = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Colonna1", type text}, {"Colonna2", type text}}), colstosplit={"Colonna1","Colonna2"}, ts= List.Accumulate(colstosplit, #"Modificato tipo", (s,c)=>Table.SplitColumn(s, c, Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {c&".1", c&".2"})) in tsThanks for your reply Rocco,
When I applied the technqiue to my data it was adding another column and renaming both .1 and 2. but the data wasn't splitting between them. The 2nd of each column was simply null.
It tried to modify using the split by position (last character from teh right), which works manually, but when I tried this:ColAreSplit= List.Accumulate(#"colstosplit", #"Capitalized Each Word", (s,c)=>Table.SplitColumn(s, c, Splitter.SplitTextByPositions({0, 1}, true), {c&".1", c&".2"}))The columns split but I only see the headers
Like I said, the code you posted did not work with my data - it split the columns but null in the second column. Then I tried a different split method described above, but since I don't know M, probably I made some mistake that does not display the rows
