Is there a way to replace column names with column locations in Power Query?-CodePudding

I am using Power Query to append multiple queries together for financial data. I am trying to add in clean-up steps after the queries are appended, but the steps continue to use the column names rather than the location. The column numbers change on a monthly/yearly basis, and I would rather write M code now to automate the refreshes rather than have to go in every time the query is pulled with new columns and delete and re-add steps. Below is my code that is auto-generated when I replace null values with zeroes. Is there a way to change the names ("201501") with an index or location?

= Table.ReplaceValue(#"Merged Columns",null,"0",Replacer.ReplaceValue,{"201501", "201601", "201701", "201801", "201901", "201902", "201903", "201904", "201905", "201906", "201907", "201908", "201909", "201910", "201911", "201912", "202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012", "Budget Ref"})

CodePudding user response：

You might try:

= Table.ReplaceValue(#"Merged Columns",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Merged Columns"))

CodePudding user response：

Suppose your table columns are A, B, 101, 201, 301, Z, where only the numeric columns change names.

You can use the Table.ColumnNames function to return them as a list

{ "A", "B", "101", "201", "301", "Z" }

and then filter this list to choose only the ones you want to do a replacement on.

= Table.ReplaceValue(
      #"Merged Columns", null, "0", Replacer.ReplaceValue, 
      List.RemoveLastN(List.RemoveFirstN(Table.ColumnNames(#"Merged Columns"), 2), 1)
  )

I've chosen to remove the first two column names and the last column name. Adjust as appropriate for your use case.