Table.TransformColumnNames - Insert Space Between Capitals

[DonalMc]

Does anyone know if it would be possible to use Table.TransformColumnNames to turn something like "IssueIndicatorStatus" into "Issue Indicator Status"

With @mike_honey  and @arify  help, I have already managed to remove a common string from a bunch of column names with the below, and am wondering if there is a similar method to bulk add spaces.

#"RemovedbwPCI" = Table.TransformColumnNames(#"Removed bwPCache", (columnName as text) as text => Text.Replace(columnName, "bwPCI", "")),

Thanks!

[mike_honey · Accepted solution]

Buried in that thread is my post linking to my demo file for this challenge - it covers your scenario too:

https://community.powerbi.com/t5/Desktop/Auto-rename-all-Query-columns-e-g-to-remove-underscore/m-p/...

Apply that to your code would be:

#"RemovedbwPCI" = Table.TransformColumnNames(#"Removed bwPCache", each Text.Trim(Text.Proper(Text.Combine( List.Transform( Text.ToList ( _ ) , each if _ = Text.Upper(_) then " " & _ else _ ) , ""))))

[DonalMc]

Hi Mike - thanks again for that. I can't believe I missed that - I actually had your Power Query demo - Renaming Columns automatically open on my desktop.

When I add the column I get Expression.Error: There weren't enough elements in the enumeration to complete the operation.

#"Add Spaces" = Table.TransformColumnNames(#"RemovedbwPCI", each Text.Trim(Text.Proper(Text.Combine( List.Transform( Text.ToList ( _ ) , each if _ = Text.Upper(_) then " " & _ else _ ) , "")))),

[DonalMc]

@mike_honey I played around with it some more and got it to work.

Thanks an mill for the help - have a great Christmas and I hope you're not sweltering too much.

[DonalMc]

And BTW - just in case any one else hits the same ExpressionError that I got, my mistake was adding Mike's code in the middle of the steps instead of at the end.

[hnguy71]

Hi @DonalMc ,

It's possible and @Zubair_Muhammad had a really great solution here .  I've made modifications to it to match your criteria:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PTfUvyUgtCkmtKFGK1YlWKkkthjCKU9Lc0lKUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
    let
        MyList = Text.ToList([Column1]),
        ListCount = List.Count(MyList),
        ListArr = {"A".."Z"}
    in
        Text.Combine(List.Generate(()=> [
            a = 0, 
            b = MyList {a}
        ],
        each [a] < ListCount, 
        each [
            a = [a] + 1, 
            b = if List.Contains(ListArr, MyList {a}) then " " & MyList {a} else MyList {a}
        ], 
        each [b]
        )))
in
    #"Added Custom"

So, pretty much you need to create a new custom column with a function to find the string and add a space in front of it.