Forum Discussion

JamieMcFadden's avatar
JamieMcFadden
Regular Visitor
4 years ago
Solved

Reading to a new table only the text values

Hi all!  I have a table containing numeric values in some cells, and character values in other cells (as well as some cells that are blank and have null values).  I show a simple example below.   I...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can do a single column like this:

    Table.TransformColumns(
    Source,
    {{"Col1",
      each if (try Number.FromText(_) otherwise null) = null then _ else null,
      type text
    }})

     

    To transform all of the columns, we can make it more dynamic as follows:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KN1TSUTIFYmcXIKEUqwMWNAKyDQ1AAjpKBjBBYyDH0QlIGBlA1MfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entity = _t, Col1 = _t, Col2 = _t, Col3 = _t]),
    fn_num2null = (txt) => if Value.Is(try Number.FromText(txt) otherwise null, Number.Type) or Text.Length(txt) = 0 then null else txt,
    TransformList = List.Transform(Table.ColumnNames(Source), each {_, fn_num2null, type text}),
    #"Transformed Columns" = Table.TransformColumns(Source, TransformList)
in
    #"Transformed Columns"

     

JamieMcFadden's avatar
JamieMcFadden
Regular Visitor
4 years ago

Thank you again for your help.  I have been able to complete the queries for the client with your help.  I have also gained a better understanding of the TransformColumns() and List.Transform() functions, and the try/otherwise construct.

 

To specify the list, I just listed the columns I wanted to transform in the M code.

 

Could you provide some advice on how to specify exactly the list I want rather than specifying them?  For one of the queries, all the columns I want to transform (and only those columns) start with a digit, such as "1. Balance", "1.a SubBalance", etc.  For another query, there are about 45 columns, of which most (42) I want to transform, so it seems just excluding the other three would be simpliest.  Thanks.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

To get a list of all columns that start with a digit, you could filter the list like this

List.Select(Table.ColumnNames(Source), each List.Contains({"0".."9"}, Text.Start(_, 1)))

You could also use Text.At(_, 0) instead of Text.Start(_, 1) in the above.

 

 

To get a list of all column names except {"Col1", "Col2", "Col3"} you can use a generic filter condition like

List.Select(Table.ColumnNames(Source), each not List.Contains({"Col1", "Col2", "Col3"}, _))

 Or you could use more specific list functions like List.RemoveItems or List.Difference

List.RemoveItems(Table.ColumnNames(Source), {"Col1", "Col2", "Col3"})