Forum Discussion
Doing operations on columns dynamically
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.
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.
1 Reply
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.