Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

Dynamically trim column values

I receive a response through API call and I want to convert anything but the first column to text and then  TRIMMED, CLEANED The first column is always integer and it will always be there but every...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    I don't think you can do much better. Maybe sticking a buffer or two in there will help a bit?

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAAUcwCeMpODk5oQgEg4BSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t]),
    ct = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", type text}, {"col3", type text}}),
    ColsToTransform = List.Buffer(List.Skip(Table.ColumnNames(ct))),
    TransformDefinition = List.Buffer(List.Transform(ColsToTransform, each {_, (txt) => Text.Clean(Text.Trim(txt)), type text})),
    Result = Table.TransformColumns(ct, TransformDefinition)
in
    Result

     

AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

I don't think you can do much better. Maybe sticking a buffer or two in there will help a bit?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAAUcwCeMpODk5oQgEg4BSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t]),
    ct = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", type text}, {"col3", type text}}),
    ColsToTransform = List.Buffer(List.Skip(Table.ColumnNames(ct))),
    TransformDefinition = List.Buffer(List.Transform(ColsToTransform, each {_, (txt) => Text.Clean(Text.Trim(txt)), type text})),
    Result = Table.TransformColumns(ct, TransformDefinition)
in
    Result