Bulk Column Rename

Power Query
1

Hi All,

Just wondering if there was a way within the Query Editor either through a Function or M to bulk rename columns in a table?

I have quite a few SQL tables to import and wanted to know if there was quicker way to rename columns.

Example:

Scenario 1 (Upper Case Words)

SQL Column Name- ApplicationID
Output: Application ID

Scenario 2 (Special Chars)

SQL Column Name- Application_ID
Output: Application ID

Thanks
Ashton D’sa

2

@adsa ,

Imke Feldmann has a terrific blog entry on this topic:

I hope this is helpful.

  • Brian
3

@BrianJ ,

Once again you saved the day!

Your recommendation also pointed me to this video & blog that I found very useful!

1 Like
4

@adsa If you don’t want to selectively replace the names then what I prefer to do is create a List containing both Original and the New name, paste this in Advaned Editor:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "NY65DsIwEER/ZeXaBTe0IQU06aI0VgrLXgkLO2vFBIm/h4lF84p5s4cxaqu02gDN4oOoURu1QwT0A9nJ0xA8V7OHAVpJeXnxXNb4gBhobeLZlnXK2eRk9v/OER2g5RgpP2TiKk4QQLeU4DR18g5cV6wv0VXkWatnVIHb706t9PKp7gIH3CUxNTnHYCeHG+MX",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ ProductCategoryKey = _t, #"Category Code" = _t, Category = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "ProductCategoryKey", Int64.Type },
            { "Category Code", Int64.Type },
            { "Category", type text }
        }
    ),
    OriginalName = Table.ColumnNames ( ChangedType ),
    NewName = { "Product Category Key", "Category Sort", "Product Category" },
    Combined = List.Zip ( { OriginalName, NewName } ),
    Result = Table.RenameColumns ( ChangedType, Combined )
in
    Result
1 Like
5

@adsa ,

Great – glad that worked for you. Thanks for the DataChant reference – I hadn’t seen that one prior. When it comes to Power Query, the combination of Raviv and Feldmann is about as good as it gets (I’m sure there’s a Chris Webb article we could throw in the mix here too…

Good seeing you back on forum, hope all is well.

  • Brian
6

Thanks @AntrikshSharma

I will try this out as well

7

Thanks @BrianJ