Bulk Rename Columns with Spaces in Power BI

A generally best practice in Power BI is to avoid naming columns in ways that won’t make sense to users so things like CustomerFirstName should be broken out with spaces between the words. The problem is that when faced with a big wide 64 column wide table, the only way to clean up the names is to one by one double click on each column name, clean it up and hit enter. This is such a pain that often folks will just blow past this leaving model with unfriendly names that looks ugly to end users.

In this video we’re going to show you how to use a CSG Pro built tool to generate the code that cleans all the columns names at once and lets you easily make small changes where needed.

Here’s the code:

//Copy the following into the Advanced Editor of a new blank query. Then rename the query something like fnRename.let

    fnCleanText = ( TextToClean ) => 
    Text.RemoveRange(
        List.Accumulate(
            Text.ToList(
                TextToClean
            ),
            "X", /*Starting with a single uppercase char makes the code easier.*/
            (SoFar, ThisElem ) =>
                if
                    /*The last char is lower case and the current char is upper case*/
                    Text.End( SoFar, 1 ) = Text.Lower( Text.End( SoFar, 1 ) )
                    and
                    ThisElem             = Text.Upper( ThisElem )
                    and
                    ThisElem <> " "
                    and
                    Text.End( SoFar, 1 ) <> " "
                then 
                    /*Insert a space before the last char*/
                    Text.Insert(     
                        SoFar, 
                        Text.Length( SoFar ), 
                        " " 
                    )
                    & 
                    ThisElem
                else 
                    SoFar & ThisElem
        ),
        0,
        1
    ),

    fnZipToM = ( ZipToM ) =>
            " Table.RenameColumns( YOUR_LAST_STEP_NAME_GOES_HERE, " &
            "#(lf)" & /*Linebreak*/
            "    {" & 
            "#(lf)" & /*Linebreak*/ 
            Text.Combine(
                List.Transform( 
                    ZipToM, 
                    each 
                        "        { """ & 
                        _{0} & /*First inner list string (old column name)*/
                        """, """ & 
                        _{1} & /*Last  inner list string (new column name)*/
                        """ }" 
                ),
                "," & 
                "#(lf)" /*Linebreak*/ 
            ) & 
            "#(lf)" & /*Linebreak*/
            "    }" &
            "#(lf)" & /*Linebreak*/ 
            ")",


    fnCleanTable = ( TableToClean as table ) => 
        fnZipToM(
            List.Zip(
                {
                    (               Table.ColumnNames( TableToClean )          ),
                    List.Transform( Table.ColumnNames( TableToClean ), fnCleanText )
                }
            )
        )
in
    fnCleanTable