Forum Discussion
More efficient way to keep only numbers in multiple columns
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!
10 Replies
Many thanks, that's a good idea which will certainly save typing
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","-","."} ) }}
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 TransRowto 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
JimmyThanks 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.
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))
