MSPTDA 10: Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column
MSPTDA 10: Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column


54 thoughts on “MSPTDA 10: Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column”

  1. Faraz Shaikh says:

    M have the way of video.. good information.. thank you for sharing & making excelisfun 😇 for all

  2. sahar kathier says:

    Thank you so much

  3. RJ Bush says:

    My head hasn't stopped spinning since MSPTDA 9. I think I will do as much as I can in the UI first before deciding on applying custom M code.

  4. ExcelIsFun says:

    Homework is posted below video.
    Bill Szysz silent movie that shows how to write Custom Function seen in this video with mostly the user interface (silent movie is easy to follow): https://drive.google.com/open?id=1v7heN-GwV7OHN1vGTLHOxTujI8QVRmVJ

  5. Eric Giroux says:

    Hi Mike,

    Thanks to you and Bill, that formula was mind-blowing…power query offer so many capabilities, thanks for showing us the ways. 😉

  6. Geert Delmulle says:

    Wow! The similarities between PQ and PP (DAX) are greater than I imagined.
    The fact that you can do that ‘EARLIER’ trick in PQ is amazing. Transcending table boundaries is powerful technique that creates a lot of possibilities, and you just showed one. And all that M-code programming all over the place: very powerful.
    BTW: very good of you to start referencing other queries in stead of copying them: more efficient.
    Q: in your explanations on several occations you used the term “steps” where —in my opinion— you should have said “queries”. Steps are parts of a query. I find it clearer not to mix them.

    Finally: next video, and BOOM, another 2K of subs! Just 10K more and you’ve reached the 500K milestone. Things are really growing fast!
    Maybe you should contemplate reaching 1M subs before this series is well and truely over! 🙂
    See you on the next one and the previous one, because we’ll be watching them again!

  7. Bill Szysz says:

    Thanks Mike for your SUPER explanations (highest level of teaching) :-))
    Here is the link to my silent movie on YT (if anyone would like to watch)
    https://youtu.be/XGPRZQl6ppw

  8. marshal115 says:

    Hi Mike. Thanks for bringing this topic for all of us on your channel officially. The idea of using custom function becomes even clearer. Will be looking forward to the next videos. And as always, big thumb-up!

  9. Syed Muzammil Mahasan Shahi says:

    Thanks Mike for this EXCELlent video

  10. John Borg says:

    My Head is spinning, but amazing Mike and thanks!!! 🙂

  11. Kamran B says:

    Great video Mike

  12. Excel.i Adam says:

    This is more difficult M, but still very interesting

  13. Karl Jolivet says:

    Great video again. The creating a list is something that just saved so much time for me. Thank you

  14. Leila Gharani says:

    Amazing stuff! Thank you 🙂

  15. Sandeep Kothari says:

    Wonderful – video & notes & M code. Great!

  16. Dave Bowman says:

    Thanks for another great vid, finally booked my MOS Excel Expert exam, at 52 I should have done it years ago, hope to get my Office Master certificate within a year or so. Thanks for all the education and your enthusiasm.

  17. Chris Mancinelli says:

    Mike this was amazing! Great video

  18. Giuseppe Confalone says:

    Thank you very much for this amazing solution and continued investment in creating fun tutorials 🙂

  19. Redouane Bouteldja says:

    Hello

    how to import images into a table from a folder of a computer on Power Query Excel?

    See the video on YouTube

    https://www.youtube.com/watch?v=LjzZcRjyB5Q&t=323s

    I thank you in advance for putting a vedio on this subject

  20. Victor Friesen says:

    Mike & Bill = M-Power-Combo !!! Absolutely amazing !!! It seems I underestimated the capability of PQ….

  21. Maneshkumar Zaveri says:

    Crazy Crazy Crazy times…. but get fun to see how data can be transformed.. As always great video Mike.

  22. Ismail Ismaili says:

    you are always updating my mind to better thank you Mr. Mike

  23. Malina C. says:

    A 100 like is mine 😉

  24. Hao Yu says:

    I dont get it… How does PowerQuery know what "x" refers to? Can anyone please help?

  25. Nis 007 says:

    I got the concept but i find it too complicated.
    I need to watch it more than two times i guess.

  26. RRR program says:

    Ohhhhhhh myyyyyyy….I doubt, if I will ever use power query to this extent…. This wasn't easy 😀

  27. Geert Delmulle says:

    Reaching the 500K subs milestone: just give it another couple of days…

  28. Doris sweanapo says:

    This section was more difficult compared to the other videos, but I still enjoy it. Thank you Mike for another great video!

  29. Syed Talha Manzar says:

    Hello Sir,
    I am wondering that I don't know how to extract months from the dates lets say I have a data table consists of dates like 9/4/2018 etc and what I need to extract September only from the dates. How can I do this in PQ?

    Regards

  30. Peter Myran says:

    This series is terrific. Intense M coding esp. the end functionfunction joining the table with the bufferred table for the date lookback. It will be very instructive to see the implementation in DAX in a later episode. Always grateful, Mike!!

  31. linelson says:

    OMFG, I need watch more time step 05 !

    I need learn polish kkk

    Other great video, thanks!!!!!!

  32. Daniel Weikert says:

    Could you show it with earlier?

  33. Saurabh Agrawal says:

    Hi Mike,

    Is there a way to replace values in power query using wild card character like we do in excel. Example: like in above text we can write power* and it will replace entire text after power.

  34. Jamie Rogers says:

    Hi Mike. A quick question on this video: in Step03 you changed the dates to the start of the month, calculated min/max and datelist to create a table, and then converted to the start of the month. This new manufactured table is essentially the same as the initial 'Calculated Start of Month' table a few steps previous. Was there are reason for this (other than for tutoring purposes 🙂 )?

    Am loving your series here and all your BI videos in general. Thanks for the work you put in.

  35. jonmon says:

    Thanks this was great! I’ve been trying to figure out how to do this for a week and then this video magically appeared.

    I tried using this approach in a 50K row data table using Power Query in Excel. It worked but took a LONG time to return the results, and after closing Power Query and loading to the data model it wouldn’t load. I waited 2 hours and then abandoned. I triple checked that I had the buffered table and custom function right and it looks good.

    Any thoughts?

  36. Jack Odum says:

    I don't really understand the use of Moving Annual Total. Can someone please explain?

  37. Mark Haggett says:

    Great video in an excellent series, Mike. Table.Buffer + CustomFunction = Mind.Blown.

  38. Jan Willem van Holst says:

    Great video. Thanks. 22:05 besides the performance you needed Table.Buffer in order to maintain your sort. If you hadn't used it you would have lost your sort.

  39. Muuip says:

    27:45 "in Excel, we cannot delete the column "A" where another function column "B" is attached to it". If the function column "B" is selected and its frame is selected with right-click with a slight drag to the right and back to original place, a menu appear with the option "Copy here as Value". After doing this, column "A" can be deleted. This only works if no filter are applied to the table.

  40. Muuip says:

    Table.Buffer() was a great help to me, since Excel and Power Query multiple-columns-sorting do not have same results + what is seen as result from Power Query does not always equal what is saved in the Cache. Wanting to delete duplicates and only Keep the top entry of each Group it was impossible to execute without Table.Buffer(). A Great tool!  There is probably a better way to retain the minimum of each group, but it worked. I guess it can be done using the add column with custom function depicted in this Video. Got to figure this one out.

  41. Sayan analytics says:

    OMG!! This is terrible..Thanks for showing this awesome trick..

  42. Radioguy00 says:

    Impressive.

  43. Gentle Raj says:

    It took a while to grasp that as soon as you do custom column there is external table added which has same content of buffered table and then select rows , conditional criteria and custom function x does it work. After getting this it was smooth ride.
    External table and internal table does the trick. Great Mike. Amazing trick.
    Hope my understanding is correct.

  44. Chris Arguello says:

    I do a lot of year over year analysis for sales by product SKU. Is this possible to do in Power Query?

    For example, I want 4 columns:
    (1) Product
    (2) Sales
    (3) 2018 Year
    (4) 2019 Year

    This can be done very easily with a pivot table. Just not sure how to do it with power query. Thank you!

  45. Joe says:

    I do not understand what he is doing on this case.

  46. Blake says:

    For adding the custom column MAT on step 14 for some reason the M code Intellisense was making it very difficult to edit the code when jumping between web browser window and PowerBI editor. It would also not accept the code, always claiming that comma was missing even it typed out perfectly, with the "OK" button greyed out in the custom column editor. I had to disable Intellisense in options, restart PowerBI, and then had no issue typing the code which worked perfectly the first try.

    Did anyone else face this issue?

  47. Alan Jiang says:

    MAT from this video only worked fine for the first 12 months, after which the number didn't add up anymore for each product. That was because in the function Date.AddMonths the example used -11. When Mike added 2 more years of data (2019 and 2020) the number -11 should have been changed to -55. One would argue we only need annual running total not going forever. If that was the case the video couldn't cope with this requirement. It needs reset after every 12 months.

  48. Ed says:

    So million $ question, how does Bill Szysz come up with this stuff?Second million # question, how does this perform with table with millions of rows?

  49. Tom R says:

    That was pretty impressive! I enjoyed the session and got lost a little (I understand the process but don't know if I am ready to repeat it) but really cool! It's amazing how many tools are available with M code. Off to MSPTDA11 ! Thanks again!

  50. erwerwe werwer says:

    I live in NZ which has a different date format than the US so PBI only recognizes my dates in text (ugh!!) – which means this formula won't work. Frustrating. Any solutions/work arounds, Mike?

  51. Anis Dadani says:

    Very complex however very well explained and taught

  52. Jan Willem van Holst says:

    I think it is better practice to use named parameters or unnamed parameters and not combine them. In this instance the nested function would look something like:

    = Table.AddColumn(BufferedTable, "MAT", (outer) => List.Sum(Table.SelectRows(BufferedTable, (inner)=> inner[Date] >= outer[DateOneYearBack] and inner[Date] <= outer[Date] and inner[Product] = outer[Product])[MonthlySales]),type number)

  53. Armond Nazarian says:

    Thanks for another great video! 🙂

  54. lmk001 says:

    Going to have to practice some easier examples for this one to make sense. Difficult. Very Power though

Leave a Reply

Your email address will not be published. Required fields are marked *