Hi all,
I would like some help on how to rename a column header by referencing the column position rather than the name.
My file will always have two months, current and prior. I would like to create a formula to calculate the month over month change as Current Month - Prior Month.
Problem: If for example my month columns are April and May, when I rename April to Prior Month and May to Current Month, all works well until the month changes. If for example my months are now June and July, when power query reaches the step to rename the column, I get an error because the M Code is looking for April and May to rename (which are no longer there).
Is there a way to tell power query to search for example column 3 which contains a month name and rename it Prior Month, then search for column 4 and rename it Current month?
That way, the name of the month will not a cause an error each time it changes?
Thanks in advance
Hi Bob, can you upload a sample file with your query? It will be easier for you if we give you a functional solution rather than a general theoretic solution.
If you prefer a generic response, here it is:
Yes, it is possible to rename columns, you can get the existing columns names dynamically with :
Table.ColumnNames(#"PreviousStepName")
To get the month from a column:
=#"PreviousStepName"[ColumnName]{0} - this will take the first value in that column
You can extract the column name based on its position:
Table.ColumnNames(#"PreviousStepName"){2} will return the name of the third column (ColumnNames returns a zero based list, column 1 is item 0 in list, column to has index 1 in list, and so on)
Thanks Catalin, this is very helpful.
