Power Query: Rapidly Renaming Columns
3 January 2018
Welcome to our Power Query blog. This week I look at renaming many columns in one step.
Clearly, renaming one column name is not difficult to do manually, but I am looking at the situation where I have the column names below:
Many of the column headings have spaces in them, and I want to put an underscore (_) in instead to prevent any problems that spaces could cause. I create a ‘New Query’ in the ‘Get and Transform’ section on the ‘Data’ tab, and choose to create the query ‘From Table’. There is a function in M devoted to transforming column names:
Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table
The description for this function in Microsoft’s Power Query help pages is:
Transforms column names by using the given nameGenerator function. Valid options: MaxLength specifies the maximum length of new column names. If the given function results with a longer column name, the long name will be trimmed. The comparer is used to control the comparison while generating new column names. Comparers can be used to provide case insensitive or culture and locale aware comparisons. The following built in comparers are available in the formula language:
Comparer.Ordinal: Used to perform an exact ordinal comparison
Comparer.OrdinalIgnoreCase: Used to perform an exact ordinal case-insensitive Comparison
Comparer.FromCulture: Used to perform a culture aware comparison.
The best way to see how this works, is with an example. To replace the spaces in my column headings with an underscore, I use the following M language:
Table.TransformColumnNames(Source, each Text.Replace(_, " ", "_"))
Essentially, this goes through my source data and each time it comes across a column name, it looks for a space and replaces it with an underscore. I need to apply this to my query.
If I use the fx button, as highlighted above, I can create a new step to transform my columns. I enter my M function and use the tick icon to create my new step.
All my columns have been updated with an underscore instead of a space and I can load the data into my spreadsheet with my newly named columns.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.
