Dynamic Replacement

[yevhen]

let
    Source = Excel.CurrentWorkbook(){[Name="rename_delete"]}[Content],
    ReplacementList = {"0".."9", "@", "&", "%"},
    SelectCol = List.Select(Table.ColumnNames(Source), each not Text.Contains(_, "*")),
    RenameCol = List.Transform(SelectCol, each {_, Text.Upper(Text.Replace(_, "1", ""))}),
    RemovedOtherColumns = Table.SelectColumns(Source, SelectCol),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,RenameCol)
in
    RenamedColumns

Need help on how to make the replacement more dynamic. For example, when a new column is added, a query checks if it has numbers or special characters in a name and removes them.

 

[alex78]

Maybe,

let
A = Excel.CurrentWorkbook(){[Name="rename_delete"]}[Content],
B = each List.Transform(_, each Text.Select(Text.Upper(_), {"A".."Z"})),
C = List.Zip(List.Select(List.Zip({Table.ColumnNames(A),Table.ToColumns(A)}), each List.NonNullCount(_{1}) > 0)),
D = Table.FromColumns(C{1}, B(C{0}))
in D

 

[Automatrix]

Here's a pivot/unpivot-based solution. It assumes that you only want to keep the first character in the column name, and make it uppercase. All steps use the UI (no custom M code).

let
    Source = Excel.CurrentWorkbook(){[Name="rename_delete"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, 1), type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Extracted First Characters",{{"Attribute", Text.Upper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Uppercased Text", List.Distinct(#"Uppercased Text"[Attribute]), "Attribute", "Value", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"