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.
@ExcelOffTheGrid
2 жыл бұрын
Of course!!! Why didn't I think of that. Great tip 😊
@OakleyTurvey
2 жыл бұрын
@@ExcelOffTheGrid 😊 I always look for the simplest solution...it helps me understand it 😊😊
@CeliaAlvesSolveExcel
2 жыл бұрын
Nice one, Oakley!
@ExcelOffTheGrid
2 жыл бұрын
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 😊
@GrainneDuggan_Excel
2 жыл бұрын
@@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!!
@davida.taylor8444
Жыл бұрын
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!
@ExcelOffTheGrid
Жыл бұрын
That must have been very frustrating. I’m glad I could help.
@CeliaAlvesSolveExcel
2 жыл бұрын
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! :)
@ExcelOffTheGrid
2 жыл бұрын
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 😀
@CeliaAlvesSolveExcel
2 жыл бұрын
@@ExcelOffTheGrid don't we all do? 😁
@elenazhu2794
Жыл бұрын
I've been struggling with a similar case for a while. This just saved me! Thanks!
@ExcelOffTheGrid
Жыл бұрын
I’m glad I could help 😀
@henrygultom3411
5 ай бұрын
Thank you!!!! You make my daily job easier.
@ExcelOffTheGrid
5 ай бұрын
Glad to hear that! 😁
@planxlsm
Жыл бұрын
4:00 Advanced Editor
@decentmendreams
Жыл бұрын
Awesome trick . Exactly what I was looking for . I like the later one . Thank you so much .
@ExcelOffTheGrid
Жыл бұрын
Glad you liked it.
@enricomendiola9952
7 ай бұрын
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.
@excelemployeeleavetracker1274
Жыл бұрын
Wow...thank you for this. Both approaches are excellent.! Keeping this in my saved list.
@ExcelOffTheGrid
Жыл бұрын
Great news! Glad I could help.
@rpopecpa
Жыл бұрын
Excellent solution!! This is extremely useful. Thanks.
@Sumanth1601
2 жыл бұрын
This is brilliant 👍🙂 Love your content on power query. Please make more videos 😉
@ExcelOffTheGrid
2 жыл бұрын
Thank you! Will do!
@lexh7714
Жыл бұрын
very useful
@ExcelOffTheGrid
Жыл бұрын
Thanks 😊
@kennethstephani692
2 жыл бұрын
Great video!
@wayneedmondson1065
2 жыл бұрын
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!!
@ExcelOffTheGrid
2 жыл бұрын
I’ve now worked through some even easier solutions. Maybe I need to do another video at some point.
@engahmedelbendary1566
2 жыл бұрын
thx alot of these good Solutions and i want to explane how to skip dynamicaly empty columns or rows
@ExcelOffTheGrid
2 жыл бұрын
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
@zaighamuddinfarooqui1705
2 жыл бұрын
Thanks for an amazing video. 💕💕💕💕
@ExcelOffTheGrid
2 жыл бұрын
No problem, you’re welcome.
@FRANKWHITE1996
Жыл бұрын
Thanks for sharing ❤
@ExcelOffTheGrid
Жыл бұрын
My pleasure. Thanks Frank.
@robinjansen4
6 ай бұрын
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.
@vasilybelkin786
9 ай бұрын
Hi Mark, file download don`t work.
@peterg4130
Жыл бұрын
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
2 жыл бұрын
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 .
@ExcelOffTheGrid
2 жыл бұрын
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)
@kebincui
2 жыл бұрын
👍👍🌹🌹🌹🌹
@ExcelOffTheGrid
2 жыл бұрын
😀 Thanks
@ibrahimsh8044
11 ай бұрын
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"}})
@ExcelOffTheGrid
11 ай бұрын
That error means you’re missing a comma. Maybe at the end of the statement.
Пікірлер: 47