cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PDG_VL
Regular Visitor

Trim transformation for list of columns

Hi,

 

I'd like to execute the Trim transformation in Power Query for a set of columns in my table.

I can do that if I select manually by clicking on the columns one-by-one and once it's done I can do the transformation. However, I'd need to select loads of columns with similar names, so I'd rather give the list of these columns and execute the transformation for each.

 

This is the current syntax:
= Table.TransformColumns(
     PreviousStep, {
         { "Column1", Text.Trim, type text},
          "Column2", Text.Trim, type text},
          "Column5", Text.Trim, type text},
          "Column15", Text.Trim, type text},
}})

Can we have something similar like this instead:
Table.TransformColumns(
     PreviousStep, {
           {"Column1", "Column2", "Column5", "Column15"}, Text.Trim, type text })

Thanks in advance!

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @PDG_VL ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

lbendlin
Super User
Super User

Something like this?

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRQUNJRUnBSgNDOIDo2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Changed Columns" = Table.TransformColumns(#"Changed Type",{},Text.Trim)
in
    #"Changed Columns"

Thanks for the answer. It does this for all the columns in the previous steps, however, is there a way to select a list of columns and all (so let's say you have Column1, Column2, Column3 and only want to do this for Column1and2)?

So you called my bluff. Fair enough.

 

If this is important to you please consider raising it at https://ideas.powerbi.com

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.