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!
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.
That's why I love the Excel community. Celia's method had my mind blown with amazing formula techniques. List.Zip has got to be a useful function for tackling about problems in a new way. Then you've blown my mind with such a simple switch around. So much to learn 😊
@@ExcelOffTheGrid Celia's Method is great when the number of columns or their order in the raw data changes. Using her List solutions makes it 'future proof'. Mark and Oakley's solutions are so much easier if you can guarantee the column placement in the raw data. I love seeing so many solutions to 1 problem - I have learned from all three of you today - Thanks!!
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! :)
I was buzzing after watching your video, a whole world of possibility that I had previously ignored - I need to spend some more time playing with List functions 😀
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!!
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.
To remove empty rows: - Filter the column remove the null values To remove empty colums: - Unpivot other the columns (this creates an Attributes column and removes null values automatically) - Pivot the columns based on the Attributes Column
Hi, This is really useful, but I have 104 date columns. Is there a way to do this without renaming every one? How would I go about using this data in pivot format, e.g. 2 years trended data by week?
If you have 104 date columns, then I doubt you have a normalized data structure. So this video isn't relevant for you. I suspect you need to unpivot the table.
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".
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 .
Yes - I agree. Thanks for letting me know. Another option I’ve found is: - Unpivot other columns - Change Type - Pivot (This also retains the original headings)
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.
Hello, Thanks. I did the same but i get error!! Expression.SyntaxError: Token ',' expected. = Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){6}, "maand 1"}, {Table.ColumnNames(#"Promoted Headers"){7}, "maand 2"}, {{Table.ColumnNames(#"Promoted Headers"){8}, "maand 3"}, {{Table.ColumnNames(#"Promoted Headers"){9}, "maand 4"}})
I've been struggling with a similar case for a while. This just saved me! Thanks!
I’m glad I could help 😀
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!
That must have been very frustrating. I’m glad I could help.
Awesome trick . Exactly what I was looking for . I like the later one . Thank you so much .
Glad you liked it.
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.
Of course!!! Why didn't I think of that. Great tip 😊
@@ExcelOffTheGrid 😊 I always look for the simplest solution...it helps me understand it 😊😊
Nice one, Oakley!
That's why I love the Excel community.
Celia's method had my mind blown with amazing formula techniques. List.Zip has got to be a useful function for tackling about problems in a new way.
Then you've blown my mind with such a simple switch around.
So much to learn 😊
@@ExcelOffTheGrid Celia's Method is great when the number of columns or their order in the raw data changes. Using her List solutions makes it 'future proof'. Mark and Oakley's solutions are so much easier if you can guarantee the column placement in the raw data. I love seeing so many solutions to 1 problem - I have learned from all three of you today - Thanks!!
Excellent solution!! This is extremely useful. Thanks.
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! :)
I was buzzing after watching your video, a whole world of possibility that I had previously ignored - I need to spend some more time playing with List functions 😀
@@ExcelOffTheGrid don't we all do? 😁
Wow...thank you for this. Both approaches are excellent.! Keeping this in my saved list.
Great news! Glad I could help.
Thank you!!!!
You make my daily job easier.
Glad to hear that! 😁
This is brilliant 👍🙂 Love your content on power query. Please make more videos 😉
Thank you! Will do!
Great video!
Thanks for an amazing video. 💕💕💕💕
No problem, you’re welcome.
Thanks for sharing ❤
My pleasure. Thanks Frank.
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!!
I’ve now worked through some even easier solutions. Maybe I need to do another video at some point.
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.
very useful
Thanks 😊
4:00 Advanced Editor
thx alot of these good Solutions and i want to explane how to skip dynamicaly empty columns or rows
To remove empty rows:
- Filter the column remove the null values
To remove empty colums:
- Unpivot other the columns (this creates an Attributes column and removes null values automatically)
- Pivot the columns based on the Attributes Column
Amazing
Hi, This is really useful, but I have 104 date columns. Is there a way to do this without renaming every one? How would I go about using this data in pivot format, e.g. 2 years trended data by week?
If you have 104 date columns, then I doubt you have a normalized data structure. So this video isn't relevant for you.
I suspect you need to unpivot the table.
Hi Mark, file download don`t work.
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".
👍👍🌹🌹🌹🌹
😀 Thanks
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 .
Yes - I agree. Thanks for letting me know.
Another option I’ve found is:
- Unpivot other columns
- Change Type
- Pivot (This also retains the original headings)
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.
Hello, Thanks. I did the same but i get error!! Expression.SyntaxError: Token ',' expected.
= Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){6}, "maand 1"}, {Table.ColumnNames(#"Promoted Headers"){7}, "maand 2"}, {{Table.ColumnNames(#"Promoted Headers"){8}, "maand 3"}, {{Table.ColumnNames(#"Promoted Headers"){9}, "maand 4"}})
That error means you’re missing a comma. Maybe at the end of the statement.