Reading to a new table only the text values

[JamieMcFadden]

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.

Is there a way to create a table from this original table, with just the character values, with the other cells null?

This is my first posting here.  If I am not following proper guidelines, or if my answer is not clear, please let me know.  Thanks!

Source Table
Entity	Col1	Col2	Col3
Org1	5	CD	(blank)
Org2	10	<null>	0
Org3	AB	20	CD
Table After Query
Entity	Col1	Col2	Col3
Org1	<null>	CD	<null>
Org2	<null>	<null>	<null>
Org3	AB	<null>	CD

[JamieMcFadden]

Thank you for your fast reply.  I will try this out.  I am busy finishing up a project and will look at it shortly.  Thanks again!

[JamieMcFadden]

I tried this and it works as I asked.  I have a follow-up question.  If I have some other columns that I don't want to apply this rule to, how can I isolate the logic you gave me to just specific columns; in this example, only Col1, Col2 and Col3.  I expanded the example to show what I mean.  Thanks!

Source Table
Entity	Col1	Col2	Col3	Col4Amt	Date
Org1	5	CD	(blank)	5	6/1/2022
Org2	10	<null>	0	10	6/1/2022
Org3	AB	20	CD	15	6/1/2022
Table after query
Entity	Col1	Col2	Col3	Col4Amt	Date
Org1	<null>	CD	<null>	5	6/1/2022
Org2	<null>	<null>	<null>	10	6/1/2022
Org3	AB	<null>	CD	15	6/1/2022

[AlexisOlson]

Replace the list of all of the table column names, Table.ColumnNames(Source), with whatever list of column names you actually want. E.g. {"Col1", "Col2", "Col3"}.

You can either specify exactly the list you want or generate it some other way like starting with Table.ColumnNames(Source) and selecting the columns you do want (based on some condition) or filtering out specific columns you don't want.

[JamieMcFadden]

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]

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"})

[AlexisOlson · Accepted solution]

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"