Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JamieMcFadden
Regular Visitor

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
EntityCol1Col2Col3
Org15CD(blank)
Org210<null>0
Org3AB20CD
    
Table After Query
EntityCol1Col2Col3
Org1<null>CD<null>
Org2<null><null><null>
Org3AB<null>CD
1 ACCEPTED SOLUTION
AlexisOlson
Super User
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"

 

View solution in original post

6 REPLIES 6
JamieMcFadden
Regular Visitor

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    
EntityCol1Col2Col3Col4AmtDate
Org15CD(blank)56/1/2022
Org210<null>0106/1/2022
Org3AB20CD156/1/2022
      
Table after query    
EntityCol1Col2Col3Col4AmtDate
Org1<null>CD<null>56/1/2022
Org2<null><null><null>106/1/2022
Org3AB<null>CD156/1/2022

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.

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.

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
Super User
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"

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors