Rename columns in Power Query when names change | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Download the example file ★

★ About this video ★
Power Query is great for extracting data from a source, reshaping it, then loading it into Excel. A standard part of the transformation process involves Power Query using column headers to reference each column. When the column headers change in the source data, it gives us a big problem.

In this video, I share two ways to rename columns in Power Query when the column names change.

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel #PowerQuery

[elenazhu]

I've been struggling with a similar case for a while. This just saved me! Thanks!

[henrygultom]

Thank you!!!!
You make my daily job easier.

[davida.taylor]

Wow, very cool trick that came in super-useful today. I was cursing some report writers who broke my workbook because they decided to change the column header names on a report that I've been using for 3-4 years. Thanks for this tip!

[decentmendreams]

Awesome trick . Exactly what I was looking for . I like the later one . Thank you so much .

[OakleyTurvey]

Love all three methods.
Another way: If the structure is the same, with detect data types off, you can manually do actions which will reference 'Column1', 'Column2', etc and then promote headers afterwards, and this will keep the original names of the headers each period as you progress. This may be useful if you are 'pdf'ing the spreadsheets each week to keep an archive.

[enricomendiola]

Great video! I tried using the solution no 1 with the modulo method. When i pivot it back the values from the prevweek was transferred to the currWeek.

[excelemployeeleavetracker]

Wow...thank you for this. Both approaches are excellent.! Keeping this in my saved list.

[CeliaAlvesSolveExcel]

Wonderful, Mark! I love playing with M code and discovering M functions, but Power Query is all about allowing the user to automate without coding. So, these approaches are simpler to apply for most people.
One of the viewers commented on my video mentioning a solution similar to your method #1. Other people reached out mentioning List.LastN(list, 3) instead of List.Select in my method.
I love all the possibilities. Some are more flexible than others to apply to scenarios where the intended columns are in different number and/or position.
Thank you for the shout-out! :)

[Sumanth]

This is brilliant 👍🙂 Love your content on power query. Please make more videos 😉

[rpopecpa]

Excellent solution!! This is extremely useful. Thanks.

[wayneedmondson]

Hi Mark. Awesome solutions! I've been looking for a way to do this. Love the direct edit of M code using Table.ColumnNames() with positional index operator. Brilliant stuff! Thanks for sharing these great techniques :)) Thumbs up!!

[engahmedelbendary]

thx alot of these good Solutions and i want to explane how to skip dynamicaly empty columns or rows

[peterg]

Can I do this with column selection? I have 15 columns, but only need to keep the first 6 and they will change their names quarterly. I tried using your M Query method but got an error saying that "value of type List can't be changed to type Function".

[Franceskineos]

Another simpler solution could be to change type before, when the column are still standard named, just after the clear first 3 rows step, than after the change type step, you can promote the header without any further step .

[robinjansen]

Why not rename column1, column2, column3 etc. manualy, instead of using the first row as headers? That sounds the most simple and robust solution and makes the query completely independent of header names in the file itself.

[ibrahimsh]

Hello, Thanks. I did the same but i get error!! Expression.SyntaxError: Token ', ' expected.

= Headers", {{Table.ColumnNames(#"Promoted Headers"){6}, "maand 1"}, {Table.ColumnNames(#"Promoted Headers"){7}, "maand 2"}, Headers"){8}, "maand 3"}, Headers"){9}, "maand 4"}})