Capitalise first text if the text not all capital letter

[roodie84]

Hi,

I am trying to clean my Column Header Name, so that:

1. Any header name that is combined will be separated from the first capital letter by "space".
2. The header name will be in Proper Case, except if the word are all capitalise.

Example is as below.

Column Header Name:
First Name
MaintActType
TECO date
FIN Date
ABC indicator
COGS
Created On

to become new and cleaned Column Header Name:
First Name
Main Act Type
TECO Date
FIN Date
ABC Indicator
COGS
Created On

my M Language 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"
​

But what i get in the end is:
First Name
Main Act Type
TECODate (wrong)
FINDate (wrong)
ABCIndicator (wrong)
COGS
Created On

How can i make this as i wanted? i have tried to figure out the right syntax for 7 hours, but I failed.
Can anybody help, please?

 

[pinarello]

I get it with:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", Int64.Type}, {"MaintActType", Int64.Type}, {"TECO date", Int64.Type}, {"FIN Date", Int64.Type}, {"ABC indicator", Int64.Type}, {"COGS", Int64.Type}, {"Created On", Int64.Type}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Column1.1", "Column1.2", "Column1.3"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1.1", "Column1.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1.1", type text}, {"Column1.1.2", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type2",{{"Column1.1.2", Text.Proper, type text}}),
#"Merged Columns" = Table.CombineColumns(#"Capitalized Each Word",{"Column1.1.1", "Column1.1.2", "Column1.2", "Column1.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
#"Promoted Headers"