Doing operations on columns dynamically

[Anonymous]

Hi,

I have a source (Excel) with values (rows) by month ids (columns).

I need to make operations on these columns, for instance, division.

The problem is that these columns headers will change from time to time, for instance, 202101 to 202201. At the moment the steps are hardcoded putting the name of the column in query, so I if I change the name of columns it will give an error. Unpivot is not an option since the structure of the file has to be mantained.

Any suggestions?

Thank you.

[edhans · Accepted solution]

HI @Anonymous 

I wrote an article on how to rename columns by their position.

Renaming A Column In Power Query Based On Position — ehansalytics

So you need to do that, rename all columns (assuming position doesn't change) to a standard name, then do any transformations on those newly named columns.

If the columns are changing position and names, I don't know how that would work. Cannot compensate for two variables like that.