Cleaning Text field columns from incoming SQL database

[RobRayborn]

I'm trying to pull in SQL data in to a Dataflow from our server.  This incoming data has Date/Time, Number, and Text data columns.  The problem is the SQL Text columns have always have have {Space} charators that fill out the remainder of the field and have to be removed using the TRIM function.  In some of these tables there are MANY Text columns and each has to have the TRIM funtion run.
I would like to know if there is a fast easy way to do this when the data is brought in, rather than having to select each individual Text column and then (hoping you don't accidently select a number column along the way) using the TRIM function?

[m_dekorte]

Hi @RobRayborn 

Give something like this a go. If your columns aren't typed instead of Table.ColumnsOfType you can pass a list with all relevant column names. Note that you have to replace "Source" twice with the previous step name in your Query.

Table.TransformColumns( Source, 
    List.Transform(
        Table.ColumnsOfType( Source, {type text}), 
        each {_, Text.Trim, type text}
    )
)

To illustrate, you can copy this code into a new blank query.

let
    TypedSource = Table.FromRows(
        {
            {"   text", "text   ", #date(2024,2,15)},
            {"   text", "text   ", #date(2024,2,15)},
            {"   text", "text   ", #date(2024,2,15)}
        }, type table[ Col1=text, Col2=text, Col3=date]
    ),
    ChType = Table.TransformColumns( TypedSource, 
        List.Transform(
            Table.ColumnsOfType(TypedSource, {type text}), 
            each {_, Text.Trim, type text})
    ),
    UntypedSource = Table.FromRows(
        {
            {"   text", "text   ", #date(2024,2,15)},
            {"   text", "text   ", #date(2024,2,15)},
            {"   text", "text   ", #date(2024,2,15)}
        }
    ),
    ChType2 = Table.TransformColumns( UntypedSource, 
        List.Transform(
            List.FirstN( Table.ColumnNames(UntypedSource), 2),
            each {_, Text.Trim, type text}
        )
    )
in
    ChType2

I hope this is helpful

[RobRayborn]

Oh my god, I just realized who was helping me on this issue!  
I am a huge fan of yours Melissa.  Many of your YouTube instructional videos have been so very helpful to me.
As for my current problem still haven't gotten it to work with my incoming SQL data.  I will keep trying, as I'm sure it's me that hasn't quite figured how to use your suggestions.
I will keep trying.

[m_dekorte · Accepted solution]

Hi @RobRayborn,

Let's try something else...

Create a new blank query and replace everything inside with the following code:

let
  TrimAllTxtCols = (t as table, optional n as number) as table =>
    Table.TransformColumns( t, 
      List.Transform(
        List.Select( Table.ColumnNames(t), 
          each if n = null 
            then List.MatchesAll(
              Table.Column(t, _), 
              (x) => Value.Is(x, type nullable text)
            )
            else List.MatchesAll(
              List.FirstN(List.RemoveNulls(Table.Column(t, _)), n), 
              (x) => Value.Is(x, type text)
            )
        ), each {_, Text.Trim, type text}
      )
    )
in
    TrimAllTxtCols

This returns a function value, you can give this query a more suitable name if you like.

Now for "t" pass it query with your incoming SQL data
When you have many rows in your table, I'd encourage you to also pass a number for "n" that tells the function how many rows to check, to see if your column contains text values.

Hope that works for you.

Cheers. 

[RobRayborn]

It's taken me a while to come back to this and try it out.
It worked perfectly. You are brilliant as always!

[RobRayborn]

Should this come after the "Navigation" step?
It also keeps generating an error, - "Token '=' expected", which is apparently supposed to come after the Table.TransformColumns.

[m_dekorte]

See the second example, where this code has been implemented.

It needs to come after the Navigation step BUT your columns have to be typed so when you see an "ABC123" beside your column names, you need to set column type first before applying it or use the second method illustrated in that example.

I hope this is helpful