Split *multiple* columns by non-digit to digit

[kcmuppet]

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: Split *multiple* columns by non-digit to digit)

 

[JGordon11]

This will split all columns in Table8

let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    tbl = List.Accumulate(Table.ColumnNames(Source), Source, (s,c)=> 
                Table.SplitColumn(s, c, Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {c & ".Alpha", c & ".Numeric"})
                                                                 ),
    ColList = List.Select(Table.ColumnNames(tbl), each Text.Contains(_, "Numeric")), 
    TypeList = List.Zip({ColList, List.Repeat({Int64.Type}, List.Count(ColList))}),
    ChType = Table.TransformColumnTypes(tbl,TypeList)
in
    ChType

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	Column2	Column3		Column1.Alpha	Column1.Numeric	Column2.Alpha	Column2.Numeric	Column3.Alpha	Column3.Numeric
2	I65	K12	D76		I	65	K	12	D	76
3	K78	E26	P88		K	78	E	26	P	88
4	F14	G65	V81		F	14	G	65	V	81
5	Y69	J12	E78		Y	69	J	12	E	78
6	Y30	D70	O20		Y	30	D	70	O	20
7	H36	C89	J26		H	36	C	89	J	26
8	I37	B22	C59		I	37	B	22	C	59
9	A34	H98	M35		A	34	H	98	M	35
10	K26	A98	O51		K	26	A	98	O	51
11	E76	F61	G29		E	76	F	61	G	29
12	Q76	I37	E76		Q	76	I	37	E	76
13
Sheet5

This will split Column1 and Column3 in Table811

let
    Source = Excel.CurrentWorkbook(){[Name="Table811"]}[Content],
    tbl = List.Accumulate({"Column1", "Column3"}, Source, (s,c)=> 
                Table.SplitColumn(s, c, Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {c & ".Alpha", c & ".Numeric"})
                                                                 ),
    ColList = List.Select(Table.ColumnNames(tbl), each Text.Contains(_, "Numeric")), 
    TypeList = List.Zip({ColList, List.Repeat({Int64.Type}, List.Count(ColList))}),
    ChType = Table.TransformColumnTypes(tbl,TypeList)
in
    ChType

 

[kcmuppet]

Many thanks for taking the time to write these queries. When I copy your code it works for me, but when I try to apply to my table it doesn't. Is there are modification I should make if - as my fields have - there are spaces in the field / column names.

The error thrown is "Expression.Error: The field 'fieldname' of the record wasn't found..

My fieldnames have spaces between the words. I tried again having replaced all the spaces with underscores, but still got the same error.

 

[JGordon11]

What are the names of the columns you are trying to split?

 

[kcmuppet]

What are the names of the columns you are trying to split?

There are many - along the lines of "Q59 visual concept appeal -package 1". I also tried them with underscores like this: "Q59_visual_concept_appeal_-_package_1". I get the same error message for both versions.

 

[JGordon11]

I think your column names may differ from what you are entering in the M code.

Try this instead

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    SplitCols = List.Accumulate({0,2}, {}, (s,c)=> s & {tcn{c}} ),
    tbl = List.Accumulate(SplitCols, Source, (s,c)=> 
                Table.SplitColumn(s, c, Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {c & ".Alpha", c & ".Numeric"})
                                                                 ),
    ColList = List.Select(Table.ColumnNames(tbl), each Text.Contains(_, "Numeric")), 
    TypeList = List.Zip({ColList, List.Repeat({Int64.Type}, List.Count(ColList))}),
    ChType = Table.TransformColumnTypes(tbl,TypeList)
in
    ChType

This splits the first and third columns since PQ column index start at zero I have {0,2} in

SplitCols = List.Accumulate({0,2}, {}, (s,c)=> s & {tcn{c}} ),

replace the {0,2} with a list of the column numbers you want to split (zero based numbering)

also replace Table1 with your table name

 

[kcmuppet]

Something strange is happening.

At the beginning of my query, I have some lines that combine the first two rows (transposing first), then promotes them as a header, and deletes the 3rd (redundant) row.

When I add the code you suggested, my result is as if first actions have been undone

let
    Source = Csv.Document(File.Contents("K:\Drive\Work\filename.csv"),[Delimiter=",", Columns=212, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type text}, {"Column88", type text}, {"Column89", type text}, {"Column90", type text}, {"Column91", type text}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}, {"Column101", type text}, {"Column102", type text}, {"Column103", type text}, {"Column104", type text}, {"Column105", type text}, {"Column106", type text}, {"Column107", type text}, {"Column108", type text}, {"Column109", type text}, {"Column110", type text}, {"Column111", type text}, {"Column112", type text}, {"Column113", type text}, {"Column114", type text}, {"Column115", type text}, {"Column116", type text}, {"Column117", type text}, {"Column118", type text}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type text}, {"Column124", type text}, {"Column125", type text}, {"Column126", type text}, {"Column127", type text}, {"Column128", type text}, {"Column129", type text}, {"Column130", type text}, {"Column131", type text}, {"Column132", type text}, {"Column133", type text}, {"Column134", type text}, {"Column135", type text}, {"Column136", type text}, {"Column137", type text}, {"Column138", type text}, {"Column139", type text}, {"Column140", type text}, {"Column141", type text}, {"Column142", type text}, {"Column143", type text}, {"Column144", type text}, {"Column145", type text}, {"Column146", type text}, {"Column147", type text}, {"Column148", type text}, {"Column149", type text}, {"Column150", type text}, {"Column151", type text}, {"Column152", type text}, {"Column153", type text}, {"Column154", type text}, {"Column155", type text}, {"Column156", type text}, {"Column157", type text}, {"Column158", type text}, {"Column159", type text}, {"Column160", type text}, {"Column161", type text}, {"Column162", type text}, {"Column163", type text}, {"Column164", type text}, {"Column165", type text}, {"Column166", type text}, {"Column167", type text}, {"Column168", type text}, {"Column169", type text}, {"Column170", type text}, {"Column171", type text}, {"Column172", type text}, {"Column173", type text}, {"Column174", type text}, {"Column175", type text}, {"Column176", type text}, {"Column177", type text}, {"Column178", type text}, {"Column179", type text}, {"Column180", type text}, {"Column181", type text}, {"Column182", type text}, {"Column183", type text}, {"Column184", type text}, {"Column185", type text}, {"Column186", type text}, {"Column187", type text}, {"Column188", type text}, {"Column189", type text}, {"Column190", type text}, {"Column191", type text}, {"Column192", type text}, {"Column193", type text}, {"Column194", type text}, {"Column195", type text}, {"Column196", type text}, {"Column197", type text}, {"Column198", type text}, {"Column199", type text}, {"Column200", type text}, {"Column201", type text}, {"Column202", type text}, {"Column203", type text}, {"Column204", type text}, {"Column205", type text}, {"Column206", type text}, {"Column207", type text}, {"Column208", type text}, {"Column209", type text}, {"Column210", type text}, {"Column211", type text}, {"Column212", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column3"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
 
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers", {"lots of long column names in here"....}),
    
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [gc gc] = "1"),

    tcn = Table.ColumnNames(Source),
    SplitCols = List.Accumulate({50,80,82,83,85,86,87,90,119,121,122,124,125,126,129,158,160,163,164,165,166,167,168,169,170,171,172}, {}, (s,c)=> s & {tcn{c}} ),
    tbl = List.Accumulate(SplitCols, Source, (s,c)=>
                Table.SplitColumn(s, c, Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {c & ".Alpha", c & ".Numeric"})
                                                                 ),
    ColList = List.Select(Table.ColumnNames(tbl), each Text.Contains(_, "Numeric")),
    TypeList = List.Zip({ColList, List.Repeat({Int64.Type}, List.Count(ColList))}),
    ChType = Table.TransformColumnTypes(tbl,TypeList)
in
    ChType

The output becomes like this:

 

[JGordon11]

change

tcn = Table.ColumnNames(Source)

to

tcn = Table.ColumnNames(#"Filtered Rows")

 

[JGordon11]

also change

tbl = List.Accumulate(SplitCols, Source, (s,c)=>

to

tbl = List.Accumulate(SplitCols, #"Filtered Rows", (s,c)=>

 

[kcmuppet]

Thanks I should have spotted that. The code now runs all the way through, but the columns aren't split as expected.

I'm wondering if instead of by non digit to digit I should try to split by number of characters. The latter seems to work if I do it manually one column at a time. Can the original code be modified something like this

    tbl = List.Accumulate({"Column1", "Column3"}, #"Filtered Rows", (s,c)=>
                Table.SplitColumn(s, c, Splitter.SplitTextByPositions({0, 1}, true),, c) {c & ".Alpha", c & ".Numeric"})