CODEWITHSUNDEEP
powerquerym
chrimaho
chrimaho

Reputation: 694

Easy way to rename multiple columns using either a text function or find & replace

Is there an easy way to rename multiple columns in a table using either a text function (like Text.Upper() or Text.Proper(), etc), or a find & replace (like Text.Replace())?

I have tried the Table.RenameColumns() function, but that requires knowing the new name of the column beforehand.

I've checked these questions, but they're not quite specific enough for my use-case:

Upvotes: 0

Views: 513

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can add a new step in your table's Advance Query Editor as below-

Table.TransformColumnNames(#"your_previous_step_name",Text.Upper)

You can check This simple tutorial for better understanding.

Upvotes: 1

chrimaho
chrimaho

Reputation: 694

Yes there is. Here you go:

let
  fun_RenameColumnsUsingTextFunction = 
    ( tbl_Table as table
    , optional lst_Columns as nullable list
    , optional fun_TextFunction as nullable function
    ) as table =>
      let
        fun_Function = if fun_TextFunction is null then Text.Upper else fun_TextFunction,
        lst_ColumnsToChange = if lst_Columns is null then Table.ColumnNames(tbl_Table) else lst_Columns,
        lst_UpdatedColumns = List.Transform(lst_ColumnsToChange, each fun_Function(_)),
        lst_Zipped = List.Zip({lst_ColumnsToChange, lst_UpdatedColumns}),
        tbl_UpdatedColumns = Table.RenameColumns(tbl_Table, lst_Zipped)
      in
        tbl_UpdatedColumns
in
  fun_RenameColumnsUsingTextFunction

let
  fun_RenameColumnsUsingTextReplace =
    ( tbl_Table as table
    , optional lst_Columns as nullable list
    , optional txt_TextToFind as nullable text
    , optional txt_TextToReplace as nullable text
    ) as table =>
      let
        lst_ColumnsToChange = if lst_Columns is null then Table.ColumnNames(tbl_Table) else lst_Columns,
        lst_UpdatedColumns =
          List.ReplaceValue(
            lst_ColumnsToChange,
            txt_TextToFind,
            txt_TextToReplace,
            Replacer.ReplaceText
          ),
        lst_Zipped = List.Zip({lst_ColumnsToChange,lst_UpdatedColumns}),
        tbl_UpdatedColumns = Table.RenameColumns(tbl_Table, lst_Zipped)
      in
        tbl_UpdatedColumns
in
  fun_RenameColumnsUsingTextReplace

For example, with this input:

col1 col2 col3
1 a x
2 b y
3 c z

We can transform it in to this:

Column1 Column2 Col3
1 a x
2 b y
3 c z

By using this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4gqlWJ1oJSMgKwmIK8E8YyArGYirlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t]),
    TextReplace = fun_RenameColumnsUsingTextReplace(Source, {"col1", "col2"}, "col", "column"),
    TextFunction = fun_RenameColumnsUsingTextFunction(TextReplace, null, Text.Proper)
in
    TextFunction

You can see more examples at this helpful GitHub repo: PowerBI-Helpers

Upvotes: 0

Related Questions