M have the way of video.. good information.. thank you for sharing & making excelisfun 😇 for all
Thank you so much
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.
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
Thanks to you and Bill, that formula was mind-blowing…power query offer so many capabilities, thanks for showing us the ways. 😉
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!
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
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!
Thanks Mike for this EXCELlent video
My Head is spinning, but amazing Mike and thanks!!! 🙂
Great video Mike
This is more difficult M, but still very interesting
Great video again. The creating a list is something that just saved so much time for me. Thank you
Amazing stuff! Thank you 🙂
Wonderful – video & notes & M code. Great!
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.
Mike this was amazing! Great video
Thank you very much for this amazing solution and continued investment in creating fun tutorials 🙂
how to import images into a table from a folder of a computer on Power Query Excel?
See the video on YouTube
I thank you in advance for putting a vedio on this subject
Mike & Bill = M-Power-Combo !!! Absolutely amazing !!! It seems I underestimated the capability of PQ….
Crazy Crazy Crazy times…. but get fun to see how data can be transformed.. As always great video Mike.
you are always updating my mind to better thank you Mr. Mike
A 100 like is mine 😉
I dont get it… How does PowerQuery know what "x" refers to? Can anyone please help?
I got the concept but i find it too complicated. I need to watch it more than two times i guess.
Ohhhhhhh myyyyyyy….I doubt, if I will ever use power query to this extent…. This wasn't easy 😀
Reaching the 500K subs milestone: just give it another couple of days…
This section was more difficult compared to the other videos, but I still enjoy it. Thank you Mike for another great video!
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?
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!!
OMFG, I need watch more time step 05 !
I need learn polish kkk
Other great video, thanks!!!!!!
Could you show it with earlier?
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.
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.
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.
I don't really understand the use of Moving Annual Total. Can someone please explain?
Great video in an excellent series, Mike. Table.Buffer + CustomFunction = Mind.Blown.
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.
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.
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.
OMG!! This is terrible..Thanks for showing this awesome trick..
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.
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!
I do not understand what he is doing on this case.
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?
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.
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?
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!
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?
Very complex however very well explained and taught
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)
Thanks for another great video! 🙂
Going to have to practice some easier examples for this one to make sense. Difficult. Very Power though
Your email address will not be published. Required fields are marked *
Save my name, email, and website in this browser for the next time I comment.