Using PowerQuery to automatically clean column names in PowerBI

What’s your favourite naming convention for tables or columns? Pascal case? Camel case? Snake case? Kebab case? Well as a developer it could be any of them depending on your preference, or even a combination. However, just pause for a moment and take off your developer hat. Now, imagine you’re a manager viewing a report, would you be expecting to see Pascal case, camel case or some other case on that report anywhere? Probably not, I mean imagine a report with a title of ‘this_is_my_report‘, that wouldn’t look very professional would it?

What you want to see is nice clear English names for all your columns and tables. Obviously we could solve this by just renaming columns manually. So, for example ‘some_field_in_some_table‘ would become ‘Some field in some table‘ or ‘Some Field In Some Table‘ (depending on how you like to capitalise words). However, this would of course become a pain in the butt if you’ve got loads of tables and columns. Luckily though, we can use PowerQuery here to help us out a bit…

We’ll use a couple of the splitter functions here, first the Splitter.SplitTextByCharacterTransition function to handle Pascal case where we’ve got transitions from upper case to lower case. We’ll also need to use another function too, the Splitter.SplitTextByAnyDelimiter function. This will handle any delimited text that’s using separators (i.e. snake case or kebab case). Using a combination of both these functions we should be able split apart any text into groups of characters, depending on certain criteria. So with this in mind, lets just create a little function to do this text cleaning. We’ll start simple then improve on this later:-

let
    SplitFunction = (TextToSplit) => let
        // Another function to split non-delimited text
        SplitNonDelimitedText = (x) => Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(x),
        
        // Another function to split delimited text
        SplitDelimitedText = (x) => Splitter.SplitTextByAnyDelimiter({"_", "-", " "})(x),
        
        // Split the supplied text
        SplitResult = if Text.Contains(TextToSplit, "_") 
            or Text.Contains(TextToSplit, "-")
            or Text.Contains(TextToSplit, " ") 
            then SplitDelimitedText(TextToSplit)
            else SplitNonDelimitedText(TextToSplit)

        in 
        SplitResult
    
    in
    SplitFunction

Running this function with a parameter value of say either ‘ExampleText’, ‘Example-Text’ or ‘Example_Text’ produces the following output. Nice, just what we need:-

{"Example", "Text"}

Ok lets build on this a little by cleaning and capitalising the text correctly. Using the Text.Clean function to remove any dodgy control characters (unlikely, but…) and Text.Proper to capitalise. Then finally a bit of code to get the column names from the supplied table and replace each of them with our ‘cleaned’ names. This is what we end up with:-

let
    CleanTableColumnNames = (TableToFormatColumnNames as table) => let                
        // Create a little function to split and clean the text
        CleanColumnName = (ColumnName) => let
            // Another function to split non-delimited text
            SplitNonDelimitedText = (x) => Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(x),
            
            // Another function to split delimited text
            SplitDelimitedText = (x) => Splitter.SplitTextByAnyDelimiter({"_", "-", " "})(x),
            
            // Split the supplied text
            SplitResult = if Text.Contains(ColumnName, "_") 
                or Text.Contains(ColumnName, "-")
                or Text.Contains(ColumnName, " ") 
                then SplitDelimitedText(ColumnName)
                else SplitNonDelimitedText(ColumnName),
                
            // Now recombine any parts using space as a word separator
            ParsedName = Text.Combine(SplitResult, " "),

            // Finally clean the text so we get nice capitalisation
            NewColumnName = Text.Clean(Text.Proper(ParsedName))

        in 
        NewColumnName,
                
        // Get the existing columns names from the table
        ExistingColumnNames = Table.ColumnNames(TableToFormatColumnNames),

        // Loop through each column name in the table and build new 
        // 'clean' names using the little split function we defined above
        NewColumnNames = List.Transform(
            ExistingColumnNames,
            each CleanColumnName(_)
        ),

        // We need to create a new list of lists with each original column
        // paired with its replacement column name
        OldAndNewColumnNamePairs = List.Zip({ExistingColumnNames, NewColumnNames}),

        // Now finally we can use the renamecolumns function to rename 
        // the columns in the supplied table with our replacement names ;-)
		RenamedColumns = Table.RenameColumns(TableToFormatColumnNames, OldAndNewColumnNamePairs)

    in
    RenamedColumns

in
CleanTableColumnNames

Now you can just use this function to apply to your table as just another step and watch the column names magically change to nice readable text. This will be much friendlier and professional looking than Pascal, snake or kebab case column names. Especially when designers are dragging these fields onto visualisations or slicers. Anyway that’s all for this one, hope someone finds this useful…