Rename all column headers removing certain text

[cgkas]

Hello to all,

I want to remove some text ("ABC.XYZ_") from all column headers.

The code I have so far is like this but for the List of second step I get error.

let
    Source = Table.FromRows({{1,2,3,4}},{"ABC.XYZ_4_NB.5", "ABC.XYZ_2_NB.9","ABC.XYZ_7_NB.3", "ABC.XYZ_K_MJ.3"}),
    RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Remove(_,"ABC.XYZ_")}),
    Output = Table.RenameColumns(Source, RenameHeaders)
in
    Output

Maybe some could help me with this.

Thanks

[d_gosbell · Accepted solution]

Try changing the Text.Remove to Text.Replace and replace with an empty string

eg.

let
    Source = Table.FromRows({{1,2,3,4}},{"ABC.XYZ_4_NB.5", "ABC.XYZ_2_NB.9","ABC.XYZ_7_NB.3", "ABC.XYZ_K_MJ.3"}),
    RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Replace(_,"ABC.XYZ_","")}),
    Output = Table.RenameColumns(Source, RenameHeaders)
in
    Output

[spags09]

is there a way to use an OR operator or remove multiple characters from the headers...

Trying to replace the underscore and NonAgg from my headers. I am able to remove the NonAgg but not able to remove the underscore and would like to. 

here is my code

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RenameHeaders = List.Transform(Table.ColumnNames(Source), each {_, Text.Replace(_,"NonAgg"," ")}),
Output = Table.RenameColumns(Source, RenameHeaders)
in
Output

[d_gosbell]

---

@spags09 wrote:

is there a way to use an OR operator or remove multiple characters from the headers...

---

I don't think you can use OR in Text.Replace, but there are a couple of other approaches

Option 1: you could use List.Accumulate to loop over a list of unwanted characters/strings and remove them

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column_1 = _t, #"Column NonAgg Sales" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column NonAgg Sales", Int64.Type}}),
OriginalColumnNames = Table.ColumnNames(Source),
ReplacementList = {"_", "NonAgg"},
RenameHeaders = List.Transform(OriginalColumnNames, (columnName) => { columnName, List.Accumulate( ReplacementList, columnName, (state, current) => Text.Replace(state, current," "))}),
Output = Table.RenameColumns(Source, RenameHeaders)
in
Output

Option 2: You could do a sequence of List.Transform to apply the text.replacements one after the other. This is probably easier to understand, but tricker to add multiple replacement strings as you have to chain the steps together

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column_1 = _t, #"Column NonAgg Sales" = _t]),
OriginalColumnNames = Table.ColumnNames(Source),
RenameHeaders1 = List.Transform(OriginalColumnNames, each Text.Replace(_,"NonAgg"," ")),
RenameHeaders2 = List.Transform(RenameHeaders1, each Text.Replace(_,"_"," ")),
NewColumnNames = List.Zip({OriginalColumnNames,RenameHeaders2}),
Output = Table.RenameColumns(Source, NewColumnNames)
in
Output

[spags09]

Those worked perfectly!! Thanks so much for the quick reply and solution. I really appreciate it 🙂 

[cgkas]

Hi @d_gosbell 

Excellent, It works perfect.

Best regards

---