Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
2 years ago
Solved

Cleaning Text field columns from incoming SQL database

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} c...
Need Help
Show and Tell
Tutorial Requests
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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's avatar
RobRayborn
Helper IV
2 years ago

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's avatar
m_dekorte
Resident Rockstar
2 years ago

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's avatar
    RobRayborn
    Helper IV
    2 years ago

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