More efficient way to keep only numbers in multiple columns

[k1s1]

Hello

I'm using this to strip out everything except numerals from ~150 columns: 

#"Convert response to numbers"=Table.TransformColumns( #"Removed Columns1" , {{"Column1 Name", each Text.Select( _ , {"0".."9","-","."} ) }, {"Column2 Name", each Text.Select( _ , {"0".."9","-","."} ) }}),

It works, but it means I have to put , each Text.Select( _ , {"0".."9","-","."} ) } after each named column.

Is there a more effeicient way to do it? - or one that involves less typing!

[Jimmy801]

Hello @k1s1 

the by far most solution is here to use Table.TransformRows, apply changes to every records by transforming it to a table and then transform the list of records back to table. It sounds complicated but using my code here should be quite easy

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNlTSAVFGQMrQ1ABIlqcWlYPEilNMixNT0pRidYhUFwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}}),
    TransRow = Table.FromRecords(Table.TransformRows
    (
        ChangedType,
        (row)=> Record.FromTable(Table.TransformColumns
        (
            Record.ToTable(row),
            {
                {
                    "Value",
                    each Text.Select(Text.From(_), {"0".."9"})
                }
            }

        ))
    ))
in
    TransRow

to basically this code here

each Text.Select(Text.From(_), {"0".."9"})

is applied to all your cells automatically... easy, isn't it?

transforms this

into this

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[k1s1]

Thanks Jimmy - it's an interesting approach.  Is there a simple way to contraint it specific columns?  Unfortunately in my big data set in about 40 columns  I need to keep the text and in aboout 110 I need to keep only the numerals.

[Jimmy801]

Hello @k1s1 

as I wrote in my post with my approach it's not needed to define the transformation of every column, but can do within one code as I'm referecing in my post as well. The idea is to get through every row... transform every single row to a table, transform the value-column and transform it back to a row... and then transform it to a table. The nice here is that you don't need to define every single column and therefore is dynamic.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

what you ask can be done, even if  "simple" is a very relative concept. I had already replied that if you could post even a fictitious example of your data and explain well what you need, someone will give you some answers. In the meantime you have to be satisfied with attempts and hypotheses (since you have not yet explained which situation you start from and where you want to arrive). I am attaching an example of what you can do with the Table.TransformColumns function that you wanted to use (but the same thing can be achieved with the Table.transformRows function)

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYsxCsAwDAP/4rXJYDlL3xKylFKo7SSl9P/UAQ3idKqVGBIZx2CB9JH6yBAUSvR+QeDmrpZXfeY9N0DM1KilSud1gk3BUsRNM2MZocY7/L2bChDDQq39", BinaryEncoding.Base64), Compression.Deflate)), 
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [c1 = _t, c2 = _t]),
    #"Duplicata colonna" = Table.DuplicateColumn(Origine, "c1", "c1 - Copia"),
    #"Rinominate colonne" = Table.RenameColumns(#"Duplicata colonna",{{"c1 - Copia", "c3"}}),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Rinominate colonne", "c1", "c1 - Copia"),
    #"Rinominate colonne1" = Table.RenameColumns(#"Duplicata colonna1",{{"c1 - Copia", "c4"}}),
   // #"Convertita in maiuscolo ogni parola" = Table.TransformColumns(#"Rinominate colonne1",{{"c3", each Text.SplitAny(_,  Text.Combine({"a".."z",","}))},{"c4", each Text.SplitAny(_,  Text.Combine({"a".."z",","}))}})
    #"Somma tutti i numeri" = Table.TransformColumns(#"Rinominate colonne1", 
    List.Transform({"c3","c4"}, each {_, each List.Sum(List.Transform(Text.SplitAny(_,  Text.Combine({"a".."z",","})),Number.From))}))

in
    #"Somma tutti i numeri"

if you have different group of column to transform in different way:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYsxCsAwDAP/4rXJYDlL3xKylFKo7SSl9P/UAQ3idKqVGBIZx2CB9JH6yBAUSvR+QeDmrpZXfeY9N0DM1KilSud1gk3BUsRNM2MZocY7/L2bChDDQq39", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [c1 = _t, c2 = _t]),
    #"Duplicata colonna" = Table.DuplicateColumn(Origine, "c1", "c3"),
    #"Duplicata colonna2" = Table.DuplicateColumn(#"Duplicata colonna", "c1", "c5"),
    #"Duplicata colonna3" = Table.DuplicateColumn(#"Duplicata colonna2", "c2", "c4"),
    #"Duplicata colonna1" = Table.DuplicateColumn(#"Duplicata colonna3", "c2", "c6"),
     transform = Table.TransformColumns(#"Duplicata colonna1", List.Transform({"c1","c3","c4"}, each {_, each List.Sum(List.Transform(Text.SplitAny(_,  Text.Combine({"a".."z",","})),Number.From))}) & 
     List.Transform({"c2","c5","c6"}, each {_, each List.Product(List.Transform(Text.SplitAny(_,  Text.Combine({"a".."z",","})),Number.From))})),
    #"Riordinate colonne" = Table.ReorderColumns(transform,{"c1", "c2", "c3", "c4", "c5", "c6"})
in
    #"Riordinate colonne"

here I made the example with two groups {c1, c3, c4} and {c2, c5, c6} but in general these lists of names can be dynamically constructed by applying selection criteria to the list of the names of all the columns:

AllColNames = Table.ColumnNames (yourTab)

group1 = List.Select (AllColNames, (c) => SatisfyGroup1Criteria (c))

group2 = List.Select (AllColNames, (c) => SatisfyGroup2Criteria (c))

[k1s1]

Thanks for your reply Rocco, 

I'm a complete beginner with power query and  appreciate your help, but don't know how to upload data or save it in the binary fashion that you are showing at the beginning of your posts, or clean it quickly of non-commerically senstive data, so I will describe below:

The data is 210 columns, of which 140 contain survey response scores 1-7, in about 3,500 rows

Unfortunately, the 1s, 4s and 7s  have text as well, like: "Completely agree7", or "7Fully understand", "Neutral4".  I want to strip those out and save as numbers. But, I don't want to lose any text in other columns. 

Those 140 columns are not next to eachother.  I could manually re-arrange them of course.

The column names are also very long, because the reflect the survery questions, so that start like "Q_23..." but are up tp 200 characters long. 

[Anonymous]

1. If you don't post a meaningful example of your data, I can't give you a specific / complete answer.
2. On the other hand, being, as you say yourself, you are inexperienced, you are unable to adapt my suggestion to your specific case.
3. do you have any idea how to overcome the impasse?

[k1s1]

Hello Rocco,

re 1  - I don't know what's not meanignfgul about the way I described the data

re 2 - I don't understand it

re 3 - No I don't, but thanks anyway for your efforts on this

[k1s1]

Many thanks, that's a good idea which will certainly save typing

[Anonymous]

depending on the complexity of your data, a solution that creates a dynamic list as the second argument of the Table.TransformColumns function might be more suitable.

I just give an idea of how to do it. The implementation is not very simple. But if you are interested and provide a sample table that I can copy, I can try to develop the idea.

names=tableColumnnames(youtable),
secondParameter=List.Transform(names, each {_, functionLikeTextSelkect})

//{{"Column1 Name", each Text.Select( _ , {"0".."9","-","."} ) }, {"Column2 Name", each Text.Select( _ , {"0".."9","-","."} ) }}

[Anonymous · Accepted solution]

the first thing that comes to mind is to merge all the columns through a character not present in the texts (#, for example) apply your rules to the union columns and then redo the division of the colonan through the added separator (# , in the example)