Split *multiple* columns by non-digit to digit

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
439
Office Version
  1. 365
Platform
  1. Windows
Hello,

Using the menus, I can split a column into two, which creates M code something like below if my column is named Column1

Power Query:
#"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)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
This will split all columns in Table8

Power Query:
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
ABCDEFGHIJK
1Column1Column2Column3Column1.AlphaColumn1.NumericColumn2.AlphaColumn2.NumericColumn3.AlphaColumn3.Numeric
2I65K12D76I65K12D76
3K78E26P88K78E26P88
4F14G65V81F14G65V81
5Y69J12E78Y69J12E78
6Y30D70O20Y30D70O20
7H36C89J26H36C89J26
8I37B22C59I37B22C59
9A34H98M35A34H98M35
10K26A98O51K26A98O51
11E76F61G29E76F61G29
12Q76I37E76Q76I37E76
13
Sheet5


This will split Column1 and Column3 in Table811

Power Query:
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
 
Upvote 0
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.
 
Upvote 0
What are the names of the columns you are trying to split?
 
Upvote 0
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.
 
Upvote 0
I think your column names may differ from what you are entering in the M code.

Try this instead

Power Query:
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

Power Query:
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
 
Upvote 0
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

Power Query:
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:

1650379751800.png
 
Upvote 0
change
Power Query:
tcn = Table.ColumnNames(Source)

to
Power Query:
tcn = Table.ColumnNames(#"Filtered Rows")
 
Upvote 0
also change
Power Query:
tbl = List.Accumulate(SplitCols, Source, (s,c)=>

to
Power Query:
tbl = List.Accumulate(SplitCols, #"Filtered Rows", (s,c)=>
 
Upvote 0
Thanks I should have spotted that. The code now runs all the way through, but the columns aren't split as expected.


1650381550929.png


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

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

Forum statistics

Threads
1,223,959
Messages
6,175,647
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top