Rename all column headers removing certain text

---

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