M Code to replace underscores in column names

Hi all,

my SQL Server DBA tells me that SQL doesn't like spaces in column names, and all tables and views in that project have unsightly underscores. In Power BI, I want to replace them with blanks, but changing all the column names manually is way too time consuming and doesn't scale well if more columns are added to the table or view.

Therefore, I wrote this little piece of M code that I put at the beginning of the query, just after navigating to the view or table I want. I hope that some of you find this useful.

let
  Source = Sql.Database("YourServer", "YourDatabase"),
  Navigation = Source{[Schema = "YourSchema", Item = "YourTableorView"]}[Data],
  // SQL Server doesn't like blanks in column names, so they come with underscores.
  // We replace underscores in any column name with blanks
  // list column names, turn the list into a table duplicate the name column, replace underscore with blank
  // turn the table into a list of lists
  ColumNames = Table.ColumnNames(Navigation),
  ColumnNamesTable = Table.FromList(ColumNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  DuplicateColumnName = Table.DuplicateColumn(ColumnNamesTable, "Column1", "Column1 - Copy"),
  ReplaceCharacter = Table.ReplaceValue(DuplicateColumnName, "_", " ", Replacer.ReplaceText, {"Column1 - Copy"}),
  ColumnNamesList = Table.ToRows(ReplaceCharacter),
  // rename the original column names with the names from the list of lists
  RenamedColumnsFromList = Table.RenameColumns(Navigation,ColumnNamesList)
in
  RenamedColumnsFromList

Let me know how you get on with this.

cheers, teylyn