CODEWITHSUNDEEP
headerpowerquerycapitalizationcolumnheader
Ahmad Rosley
Ahmad Rosley

Reputation: 1

How to properly clean column header in Power Query and capitalize first letter only without changing other letter?

I would like to clean a column Header of the table so that my column header that has a name like below:

And my desired Column Header Name to be like below:

my code is as below:

let
    Source = Excel.Workbook(File.Contents("C:\RawData\sample.xlsx"), null, true),
    #"sample_Sheet" = Source{[Item="sample",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"sample_Sheet", [PromoteAllScalars=true]),
    #"Trim ColumnSpace" = Table.TransformColumnNames(#"Promoted Headers", Text.Trim),
    #"Split CapitalLetter" = Table.TransformColumnNames(#"Trim ColumnSpace", each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " ")),
    #"Remove DoubleSpace" = Table.TransformColumnNames(#"Split CapitalLetter", each Replacer.ReplaceText(_, "  ", " ")),
    #"Capitalise FirstLetter" = Table.TransformColumnNames(#"Remove DoubleSpace", Text.Proper),
    #"Remove Space" = Table.TransformColumnNames(#"Capitalise FirstLetter", each Text.Remove(_, {" "})),
    #"Separate ColumnName" = Table.TransformColumnNames(#"Remove Space", each Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}) (_), " "))
in
    #"Separate ColumnName"

However, i get the result as below. Which is not what i wanted as all the capital letter we combined together. How do i change the code so that i get the result as wanted? I would really appreciate your help, please.

Alternatively, i changed the code to:

let
   Source = Excel.Workbook(File.Contents("C:\RawData\sample.xlsx"), null, true),
   #"sample_Sheet" = Source{[Item="sample",Kind="Sheet"]}[Data],
   #"Promoted Headers" = Table.PromoteHeaders(#"sample_Sheet", [PromoteAllScalars=true]),
   #"Trim ColumnSpace" = Table.TransformColumnNames(Input, Text.Trim),
   #"Separate ColumnName" = Table.TransformColumnNames(#"Trim ColumnSpace", each Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}) (_), " ")),
   #"Capitalise FirstLetter" = Table.TransformColumnNames(#"Separate ColumnName", Text.Proper)    
in
   #"Capitalise FirstLetter"

Unfortunately it return the result like so:

I have no idea how to play around the code anymore.

Upvotes: 0

Views: 2027

Answers (2)

AWOLKiwi
AWOLKiwi

Reputation: 29

use Table.TransformColumnNames ( #"Prior step", Text.Clean)

e.g.

#"Remove all control characters" = Table.TransformColumnNames(#"Promoted Headers", Text.Clean)

#"Remove leading and trailing white space" = Table.TransformColumnNames(#"Remove all control characters", Text.Trim)

CLUTR: Clean, Lower/Upper case, Trim, Replace values/errors

Upvotes: 1

TheRizza
TheRizza

Reputation: 2062

One way is to mark the existing spaces with something (I used "ZZZ") and restore them back to spaces at the end. Here's your code with a couple of tweaks. Thanks for your code. I was trying to do Text.Proper and your sample helped me!

let
    Source = Input,
    #"Replaced Value" = Table.ReplaceValue(Source,"[Space]"," ",Replacer.ReplaceText,{"Headers"}),
    #"Transposed Table" = Table.Transpose(#"Replaced Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Trim ColumnSpace" = Table.TransformColumnNames(#"Promoted Headers", Text.Trim),
    #"Change space to ZZZ" = Table.TransformColumnNames(#"Trim ColumnSpace", each Replacer.ReplaceText(_, " ", " ZZZ ")), 
    #"Split CapitalLetter" = Table.TransformColumnNames(#"Change space to ZZZ", each Text.Combine(Splitter.SplitTextByPositions(Text.PositionOfAny(_, {"A".."Z"},2)) (_), " ")),
    #"Capitalise FirstLetter" = Table.TransformColumnNames(#"Split CapitalLetter", Text.Proper),
    #"Remove Space" = Table.TransformColumnNames(#"Capitalise FirstLetter", each Text.Remove(_, {" "})),
    #"Separate ColumnName" = Table.TransformColumnNames(#"Remove Space", each Text.Combine(Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}) (_), " ")),
    #"Change ZZZ to space" = Table.TransformColumnNames(#"Separate ColumnName", each Replacer.ReplaceText(_, "ZZZ", " ")), 
    #"Remove DoubleSpace" = Table.TransformColumnNames(#"Change ZZZ to space", each Replacer.ReplaceText(_, "  ", " "))
in
    #"Remove DoubleSpace"

Upvotes: 0

Related Questions