Modify Column Names Using Power Query M Functions

Update – There is already a dedicated function available to do this, Table.TransformColumnNames. Thanks Imke Feldmann for the shutout. You can find her blog post here.

Recently while I was working on a Power BI model, where I need to modify multiple column names in multiple tables, I wondered is there any function already available which will let me do this with a single statement.

Why I want to format Column Names:

I imported few SSISDB Catalog tables, and one of them looks like below

I don’t want to see underscores in my report, So I started renaming each column. But I was working with multiple queries with this kind of column names. So I began looking under Power BI Query Editor tab, I found Use Headers as FirstRow (I never knew, I can demote Column Headers 🙂 ).

At this stage I thought, I could just use Replace values after this step which would replace all Under Scores. However, data in my table also has Under Scores, that I don’t want to replace. Also, I want to change column names to Proper Case. Fundamentally, I just want to modify only the first row in each query after I demoted headers, but how?

I remember Chris Webb demo about Lists in London Power BI user group; he showed how you could use Lists in Power BI. He also has a blog post about it – https://blog.crossjoin.co.uk/2016/06/21/creating-sequences-of-integers-and-characters-in-power-bipower-query-lists/

I can adopt what he described and select only one row from a list. To do what I want, I need to convert my table to a List and select the first row. Then do all the transformation’s I want on that row. Subsequently, append the transformed row table to the existing table.

Let’s see this in M Language:

At First, my query looked like below

let
Source = Sql.Database(ServerName, "SSISDB"),
catalog_catalog_properties = Source{[Schema="catalog",Item="catalog_properties"]}[Data]
in
catalog_catalog_properties

Then I chose Use Headers as the First Row

DemotedHeaders = Table.DemoteHeaders(catalog_catalog_properties)
in
DemotedHeaders

Then I changed table to List

TabletoList = Table.ToList(DemotedHeaders)

Then selected only first row of the list, which was my header column

TabletoList1 = TabletoList{0}

Then I did the transformations I want to do

//Replace Under scores and format to Proper case
ALLRows = Text.Replace(Text.Proper(TabletoList1),"_"," ")

Then I converted it to list

#"Converted to List" = {ALLRows },

then to a table

#"Converted to Table" = Table.FromList(#"Converted to List", Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error)

Now I have the table I want; header row with the changes I need. Next step, to combine with original data of my table that I left at TabletoList1 step. The beauty of M Language is, whatever transformation you do between the steps, your final result is what you chose in your “In” Statement.
So at this stage, I wanted DemotedHeaders Step. Which gives me my dataset as below

As I already prepared another table in my advance script, I removed top 1 row.

Then I used Table.Combine to combine two tables

And finally promoted nicely formatted first row as Header. Now I can easily convert this query to a function by right clicking on the query or from a new blank query. I went for a blank query, so I can polish the query, remove extra spaces and symbols created by GUI.
Function:

/*
This Function will remove all Under scores in the Column Names and changes column names to Proper Case.
It takes StepName as Parameter, which is the last Step in the Query
*/

let
FormatColumnNames = (StepName as any) =>
let
DemotedHeaders = Table.DemoteHeaders(StepName),
RemovedTop1Rows = Table.Skip(DemotedHeaders,1),
TabletoList = Table.ToList(DemotedHeaders),
TabletoList1 = TabletoList{0},
ALLRows = Text.Replace(Text.Proper(TabletoList1),"_"," "),
ConvertedToList = {ALLRows},
ConvertedToTable = Table.FromList(ConvertedToList , Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
Combine = Table.Combine({ConvertedToTable, RemovedTop1Rows}),
PromotedHeaders = Table.PromoteHeaders(Combine)
in

PromotedHeaders

in FormatColumnNames

This was an attempt  to change column names; it saves a lot of time. This function is certainly a keeper for me. It’s worth having your own Power Query functions repository, few already available online (I have not checked, modify column names available in this list or not) – https://github.com/tycho01/pquery.

Prathy 🙂