0

I have created a recurring (monthly) query tool. Each month, the name of a column will change. I would like the user to be able to type the value in a cell on the first sheet, and drive that value to become the title of a column in the table.

Thought 1: Modify the following code: #"Renamed Columns1" = Table.RenameColumns(#"Multiplied Column7",{{"Month1;BDG", "AAA"}}) to replace "AAA" with a call to a specific cell on another sheet directly in the "Renamed" step. Is this possible?

Thought 2: Manage the change as column data by doing the following: 1. Demote the current column titles using "Use headers as first row" 2. Write an IF statement if the value = "Column7" then "value of the cell on the other sheet" else [Column7]. 3. Then promote the top row to be column headers after the change.

Or is there a way easier way? My searches have come up dry!

Matthew Marks
  • 1

1 Answers1

0

Create a table in Excel that has the column name you want to use. For example, if you want "AAA", then put this in a couple cells in your sheet:

ColumnName
AAA

Select those two cells and go to Insert > Table and make it a table. (Check the box for "My table has headers".)

Under Design, you can name this table whatever you'd like, say, "ColNameTable".

Then in your code, you can use 

Excel.CurrentWorkbook(){[Name="ColNameTable"]}[Content]{0}[ColumnName]

instead of "AAA". I.E.

#"Renamed Columns1" = Table.RenameColumns(#"Multiplied Column7",{{"Month1;BDG", Excel.CurrentWorkbook(){[Name="ColNameTable"]}[Content]{0}[ColumnName]}})

This pulls in the value of the first row (index starts at zero) of the column named ColumnName in the table ColNameTable from your current workbook.

Note: You can also use named ranges instead of creating a new table. See here: How can I reference a cell's value in a Power Query

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64