Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DonalMc
Advocate II
Advocate II

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!

1 ACCEPTED SOLUTION
mike_honey
Memorable Member
Memorable Member

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 _ ) , ""))))

View solution in original post

5 REPLIES 5
mike_honey
Memorable Member
Memorable Member

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 _ ) , ""))))

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 _ ) , "")))),

 

addSpacesError.png

@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.

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
Memorable Member
Memorable Member

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.

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.