View Full Version : Working with Column Headers in Power Query

2017-04-25, 09:00 AM

When I work in Power Query on one of the sheets that I loaded from another excel workbooks I do some manipulation.

Unpivot, Reorder and some other functions. In the end if someone will change one of the columns headers in my workbook all my work will be wasted since in my steps in power query when I checked, I saw that all the manipulation I did in Advance editor are using Column Names .

Is there any other opthion to make the steps (reorder, unpivot, etc.) without using column name?

Ed Kelly
2017-04-25, 12:42 PM

Not aware of any options. The easiest solution tell the user not to change the column headers, failing that be prepared to edit the advanced editor with those changes (which is not a lot of fun) or finally consider building out a few queries within the same workbook and have your user self select based on the matching structure.

Ken Puls
2017-04-25, 05:14 PM
Yes, but it's a bit complex.

Basically, you suck in the table, then reference that query twice.

In the first, you remove all rows, then demote the headers.

In the second, you demote the headers, remove the top row, then do the work you need to do.

You then create a new query to append Query2 to Query1, promote the first row to headers, and load it out. The big challenge is that you cannot change the data types in the final query, as that hard codes to column headers, which will break your query.

2017-04-25, 06:43 PM
Another cross post (https://community.powerbi.com/t5/Desktop/Working-with-Column-Headers-in-Power-Query/m-p/163551/highlight/false#M71263).