Home Power BI Modify Column Names Using Power Query M Functions

Modify Column Names Using Power Query M Functions

by Prathy Kamasani March 14, 2017 0 comment

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

Then I chose Use Headers as the First Row

Then I changed table to List

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

Then I did the transformations I want to do

Then I converted it to list

then to a table

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 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 🙂

0 comment

You may also like

%d bloggers like this:
Skip to toolbar