Forum Discussion
Table.TransformColumnNames - Insert Space Between Capitals
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!
Buried in that thread is my post linking to my demo file for this challenge - it covers your scenario too:
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 _ ) , ""))))
5 Replies
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.
Buried in that thread is my post linking to my demo file for this challenge - it covers your scenario too:
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 _ ) , ""))))
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 _ ) , "")))),
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.
