Forum Discussion
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.
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 |
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"
6 Replies
- AlexisOlson
Super User
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" 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!
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.
