Forum Discussion
Rename all column headers removing certain text
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
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
Outputis 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
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
OutputOption 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
OutputThose worked perfectly!! Thanks so much for the quick reply and solution. I really appreciate it 🙂
