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 😀
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!
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 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!!
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"}})
