The last step to avoid hard-coding changeing data type approach by "unpivotting-->change type-->pivotting back" is a really mind blowing tip. Thanks Celia for your genius solution 👍👍👍👍
Thanks, Kebin! And thank you for reminding of that technique. I am dealing with this challenge on another project and I just built an M solution for that when I could have simply used this approach. I totally forgot about it. 😁
Many thanks, Celia. This is great. I came across this lesson via a YT search (I already subscribe to your channel). Although it doesn't *exactly* answer my query, I can see how I can use the techniques and M functions that you've shown us in Method 4, to provide a solution to my problem. Briefly, I need to sum across a variable number of up to 12 columns (i.e. the months in a financial year) to produce the Year to Date Actuals figure per row. The number of columns to sum is obtained from a forecast reference in another column. I can now see how to get the required list of columns to sum their records. I like the way you lead us through the solution slowly and carefully, showing the pitfalls you encounter on the way. Regards Ian in London, UK
Hi Celia, I just rewatched this video and it was captivating - very interesting to see how you were able to make the column names and the change type dynamic. Excellent work! The teacher in you definitely shines! Thanks for sharing.
Thanks for sharing your way of picking up the relevant M Code and guiding how to read M Code as we are building a query. Method 4 was outstanding as the requirement was for Special Accounts only, I think you could have included the Regular Accounts also, as you had done in Method 2 ie Pivot it's my thought. I really enjoyed the class. Method 3 after the out when used the Table to sum each column, it never occurred, one can use this way to total the given Columns .Thanks 🙏
Trust Me I wasted 2 days on searching how to sum with the list of columns...And most importantly ChatGPT fucked me on this...finally, Thank god, You saved me...
@@ramneeksharma4226 I'll be back with more content soon. I appreciate your feedback. Feel free to always comment if and how the lesson was helpful to you.
Hi, Jeganathan. Unfortunately, we cannot. When you concatenate text pieces the result is perceived as a text string too. You would have "[4791]" as a string instead of [4791] as a field name. But you can test it on your end and see what happens. ;-)
The last step to avoid hard-coding changeing data type approach by "unpivotting-->change type-->pivotting back" is a really mind blowing tip. Thanks Celia for your genius solution 👍👍👍👍
Thanks, Kebin! And thank you for reminding of that technique. I am dealing with this challenge on another project and I just built an M solution for that when I could have simply used this approach. I totally forgot about it. 😁
Many thanks, Celia. This is great.
I came across this lesson via a YT search (I already subscribe to your channel). Although it doesn't *exactly* answer my query, I can see how I can use the techniques and M functions that you've shown us in Method 4, to provide a solution to my problem. Briefly, I need to sum across a variable number of up to 12 columns (i.e. the months in a financial year) to produce the Year to Date Actuals figure per row. The number of columns to sum is obtained from a forecast reference in another column. I can now see how to get the required list of columns to sum their records.
I like the way you lead us through the solution slowly and carefully, showing the pitfalls you encounter on the way.
Regards
Ian in London, UK
Thank you for your kind words and feedback, Ian. I am glad the video was helpful to you. All the success in your project.
thank you for posting this useful tutorial, and to always zoom on the content, many youtubers neglect it, much appreciated
Thank you very much for watching and your kind feedback. Glad that it was helpful.
Hi Celia, I just rewatched this video and it was captivating - very interesting to see how you were able to make the column names and the change type dynamic. Excellent work! The teacher in you definitely shines! Thanks for sharing.
Thank you very much for your kind words, Gosnel. :) happy that the tutorial was useful for you.
Nice techniques! Thanks for taking the time to share this approach.
Thank you for taking the time to watch and leaving your feedback, Owen! 💚
Great video, Celia!
Thank you, Kenneth. This was a fun challenge to solve.
That's amazing. In just one video I was able to learn so much. Really appreciated. Thank you!
You're very welcome, Teodor.
I am glad that it was helpful to you.
The session was very informative. Keep up the good work. thank you
Thank you for attending the session and your feedback.
Thanks for sharing your way of picking up the relevant M Code and guiding how to read M Code as we are building a query. Method 4 was outstanding as the requirement was for Special Accounts only, I think you could have included the Regular Accounts also, as you had done in Method 2 ie Pivot it's my thought. I really enjoyed the class. Method 3 after the out when used the Table to sum each column, it never occurred, one can use this way to total the given Columns .Thanks 🙏
You're welcome, Bharathram. I am glad that this class brought you so many insights. :) Thank you so much for leaving your feedback.
Amazing video!! About to implement this on one report I have. Thanks !
You're welcome, Paula! Good luck and enjoy! :)
Ainda tentei ver em directo, mas estava podre de sono. Aqui ja eram 0h30. Irei ver assim que possivel e fazer o exercicio no Excel fornecido. Obrigada
O descanso primeiro! Com energias recuperadas aprende melhor. 😉
Obrigada pelo carinho e apoio.
Aguardo o seu feedback sobre a aula. Obrigada, Ana!
Amazing. Thanks Celia 👍
As like your other videos - this is also informative and interesting. Please keep sharing this kind of knowledge.
Thank you, Anil. I appreciate your support and feedback. 🙏
90 minutes session is good.
Thank you for your feedback, Shyam. I am glad that you took advantage of the free lesson.
Longer tutorial is good
Great! Glad that you find value in these lessons, Kebin. Thank you for your feedback.
Trust Me I wasted 2 days on searching how to sum with the list of columns...And most importantly ChatGPT fucked me on this...finally, Thank god, You saved me...
There you go! A story with a happy ending. Glad that it helped you. Thank you for letting me know. 😊
Informative video, if column headers having name then how to automate sum
Select the columns that you want to sum, go to Add Column tab, click the button Standard and then choose Add.
Hi.... after a very very long time.
Hi, Ramneek! Do you mean a long time without live lessons here?
@@CeliaAlvesSolveExcel yes
@@ramneeksharma4226 I'll be back with more content soon. I appreciate your feedback. Feel free to always comment if and how the lesson was helpful to you.
@@CeliaAlvesSolveExcel thnks a lot Celia... Please cover more advanced and powerful functions such as table. buffer and list. accumulate etc.
@1:06:05 Can't we concatenate ColumnNames list to a single text with [ before ], after ; and then use it in list.sum?
Hi, Jeganathan. Unfortunately, we cannot. When you concatenate text pieces the result is perceived as a text string too. You would have "[4791]" as a string instead of [4791] as a field name.
But you can test it on your end and see what happens. ;-)
My favorite was method4
Mine too, Vishal! :) I love playing with the M Code.