@@excelisfun Other ways? Self-Merging after adding index columns twice (from 0 and from 1) :-)) By the way....I was probably one of the first to show this technique... five years ago ;-))) It is nice to see that it is useful....and perfectly explained :-))) Thanks Mike!!!!
@@BillSzysz1 , Just like at the Mr Excel Message Board and formula solutions, people say; "It all starts with Aladin", the same is true about PQ: "It all starts with Bill Szysz"!!!! : ) Thanks for leading the way, O Master Poet and Artist of PQ, Bill Szysz!!!
Congrats, after looking 5 videos, but this is so far the best elegant and straight to the point of what I needed!!!!. You have my like to your video!!! (y)
Great explanation! I used to do the same with Merge operation. I used to add two Index columns (first starting from 0, the sesecond starting from 1) and then I merged the table with itself selecting first index column and second index column to get the offset :) The solution you provided is much more elegant :)
Another excellent video to build on the MSPTDA 09 M Code tutorial. It is an excellent foundation for doing IF functions and LOOKUP in Power Query. The video had a great explanation of Positional Index, Field Access, and Lookup operators.
Mike, I've been using PQ for the majority of a solution I'm building but have had to bring the data back into Excel to add a formula to determine if inventory is being shipped in FIFO. I can now stay in PQ and determine based on the previous row and a few other variables if a shipping line was shipped in order! I've learned so much from you and I really appreciate you so much! Thanks for all you do for us!
Umbelievable, i needed this M code a few weeks ago for a dataset containing a cumulative sales and i have to figure out monthly for every day in the month the amount of revenus. A 10000000000 thumbs up to you mike :-)
That is really illuminating. I've been trying to something similar to your looking up previous but instead with averages, maximums and minimums. It's difficult.
I needed to create my own text file and adjust the Source step for the Queries to work but that was additional practice with Power Query! Thanks for the interesting technique!
Mike, excellent video! Really wish you show us more of this PQ magic ! Any chance you may record a more advanced version of "MSPTDA 09" that builds on the concepts explained there? I ask as the powers of PQ seem to be endless every time I browse the PQ forums. But I only seem to understand the way you explain those tough hard to understand concepts.
Thank you for your videoes, they have been such a great help in terms of learning Power Query/BI! You've briefly touched upon each and when to replace it with _ in your earlier videoes. I'm still struggling a bit with understanding the aspect of each so I was wondering if you could elaborate on why you did not do it in this formula? Is there maybe some sort of easy rule of thumb I could follow?
Glad it is easy and fun for you - for me making a video all comes down to one thing: how can I tell a story that can reveal difficult concepts in a way that is not difficult : ) Thank you for your support on each video that you watch with your comments, Thumbs Ups and, of course, Katerina, your Sub : )
Mike, your way of explaining makes it so clear and easy. Thanks a lot. By the way, I found there is Intellisense in M code editor in Power BI, but not available in Excel. Would have helped a lot...
This is so cool! I used to achieve this by adding two index columns, one starts with 0 and the other one starts with 1. Then merge the table with the table by itself using the two index columns as matching columns. Then you know, I extract the value I need. I have (edit: not) tried, but I believe your way should be more efficient in terms of performance when we are during with a big table. Thanks for sharing this, Mike
I recently did something similar to get the last exchange rate of the last available day of the month. My code was not as clean as yours and now I have to reimprove it. Cheers.
Very nice and well explained. I used to add two index columns, starting from 0 and from 1 a d then merge the query with itself But this solution is much better
Thanks for the method you used to do. I had not done it that way. But, yes, most times doing a lookup will be easier. Thanks for your support, Kolyu!!!
I spend a lot of time watching your videos Mike...they are all excellent, thank you !! Glad you did a query for previous row, as I've been looking for a solution like this for some time and you have explained it brilliantly. I suppose it could be adjusted to select a cell reference from 2 rows before or more?
Glad you get to watch a bunch of vids and they are EXCELlent for you, Paul! Thanks for your support on each video that you watch with your comments, thumbs ups and of course your Sub : ) Yes, you can easily get two above also ; )
Yes, indeed, the truth about everything, Geert: Once you know how to do it, it’s not that difficult. It is just how to get something totally new into out head in the first place to know it : )
And there’s more: how to keep it in our heads (=> repeat and practice) - for my current project I’m rewatching MSPTDA, I’ve refinished the PQ part, now I’m at the DAX stage again (that context transition and CALCULATE are fantastic for aggregation - so simple, so I promoted that calculation from PQ to PP)... Now there is contrast: your Crazy Array Formulas (TM_MikeGirvin) remain difficult no matter how you slice it (no pun intended). Anyway, it’s back to work for me. It’s nice to drop you a line now and then. :-)
@@GeertDelmulle Yes, sir! I am so happy to hear that you are re-watching and refining your knowledge. Guess what? Me too! I created most of MSPTDA last year, and now that I am teaching this class at Highline (this is second week) I had to re-watch MSPTDA to remind me of the story and how students will see it, so that I could create new data sets and graded assignment. What calculation did you have in PQ that you promoted to DAx, and why? Always great to hang out on the Team with you, Geert : )
Mike, The calculation that got promoted from PQ to DAX: aggregation of a max(some date field) from the reporting periods sub-level to the project level (higher level/lower granularity). Reason: much simpler in DAX: - in PQ I had to create an extra query that aggregated using Group By and List.Max, then had to merge that query back into the fProject-table/query. But I was not allowed to merge it back into the orig table, I had to go for the option of a new query. Rather ‘indirect’ and complicated. Don’t worry: it worked. - In DAX: calculated column in the fProject-table: := CALCULATE(MAX(fPeriod[Some Date])), and... DONE! (both tables are related). The most efficient aggregation I’ve ever seen (cfr. MSPTDA15 -I think-, where you do the same using SUM). Much simpler, and more efficient. And since I learned from a certain Mike Girvin that you should always go for the most efficient solution, that’s what I did. (the thing I’n building will be used for a long time and for an ever increasing set of data). Now don’t get me wrong: I love PQ to bits, but the sheer power and efficiency of DAX is amazing. Now that I’ve arrived in the DAX stage of the development of my application, I deepen my knowledge and understanding of DAX in my quest to make it as efficient as possible. I rewatched most of the DAX videos today (excl. PBI), but the one on CALCULATE is still on the list: I know for sure that I need the knowledge in that video for my application. In some video by Mr.Excel I saw recently, I heard Bill call CALCULATE to be some sort of SUMIFS (but then much more generalized). I find that an interesting comparison. Good luck with your new class at Highline, and have fun! Your students are so lucky to have such an amazing teacher!
@@GeertDelmulle He said it was like SUMIFS cuz Rob Collie wrote that is his book. That is not really a good parallel. It changes Filter Context, in o so many ways!!! The CALCULATE videos is intense and very dense with many examples of changing the Filter Context, and also the potential pitfalls of CALCULATE and DAX... I agree with you, some DAX formulas are just so simple to create and do complicated things : )
Sir, pls make video for adding index column to sub group in power query and getting previous record to arrive how stock perform for various days. I got close price of multiple Days for multiple stocks. Thanx
Yes indeed, the freedom in an Excel Worksheet is unmatched. But Power Query and DAX both have certain tools that make certain tasks much easier, but not his one : )
8:11 mr. Excelisfun, why not use Table.skip function to skip one row in the added table and then simply subtract values and absolute them? would that be easier?
Suggested Sub-Title: How to take something easy and make it a bit hard!! An excellent reminder that PQ adds to the Transformation Took Kit but does not replace existing. Assuming that the data set is not coming from excel (reasonable thought) and that the ultimate goal is to use the Data Model, my temptation would be to leave the PQ Purgatory ASAP and finish this transformation in DAX!?! For me DAXMeasureFunFactor>M-CodeFunFactor.
But... The DAX Formula is even harder... =IF(MIN(GEPrices[Date])=GEPrices[Date],BLANK(),GEPrices[GE Close]-LOOKUPVALUE(GEPrices[GE Close],GEPrices[Date],MAXX(FILTER(ALL(GEPrices[Date]),GEPrices[Date]
"Harder" is definitely an individual judgement. DAX formula is LONG, but mostly due to referent syntax (like table nomenclature formulas in Spreadsheet Excel). The structure is to me very straight forward and the formula comes together logically and quickly. So personally, I will take DAX any day. BUT I understand that is not a universally shared view!! The "problem" With DAX: just so many (powerful & useful) functions to learn!! The number is large and the growth is exponential. But isn't that part of the FUN? :) Just wish that any new DAX available in PBID would be simultaneously available in EXCEL (DAX). Why can't we have EVERYTHING? LOL
Very good video! Although, I couldn't make it work in my case. I have a table of tables, and I need to calculate the moving range for each individual table. My main table has two columns, first with the group name, second the table related to it. In each of these tables I would like to include this code, but once I try to use this method, or it complains that my "AddedIndex" is not recognized or in the best case scenario, when using [AddedIndex][Index] I managed to get the list of the indexes... It doesn't work with {} also. Instead of each I had to use (r)=> for it to recognize my nested table...
@@ExcelExciting Thank you very much for your support... Yes, I do not know why people click Thumbs Down: maybe they have confused their right to free speech with their duty to try and make the world a better place?
This is great. I have a question though along the same lines but slightly different. Can column reference itself in the formula. for example:Table.AddColumn(AddedIndex, "Open Balance", each if [Date] = [LOAN.Start Date] then [LOAN.Amount] else AddedIndex{[Index]-1}[Open Balance]) I'm trying to use Excel Power Query to develop loan forecasting
Hi. Thank you so much for your initiative. Due to your effort , I have been learning power query. I have a big problem and I need your help, Though I had researched, I couldn't find answer. I have a dimension table with 5 thousand rows Table A. This table contains 3 columns. I also have a table (Table B) with 3 millions rows. I need to connect both tables, but I can't use a lookup with power query due to my dimension have a range, I need looking for a row based in a range. Eg: colunm 1= 1000 , column 2 = 2000. How can I select a related row if my fact contain a record = 1550 ? Is that possible use a lookup on that case? Can you help me?
Is there a video that can show me how to create a report from a linked spreadsheet? At the moment anytime I refresh the data the new info I’ve inputted goes out of whack. Help please!!!!
As always, excellent video with very helpful information, thank you very much. I have a question: supose we want to load the final query into the Data Model. I think Power Pivot would identify the last column as text because of the NULL, what would be the workaround for that?
A column with null and numbers will work find in the Data Model, for example if you added 1 to the column in a DAX Calculated Column, it would work fine. Probably, you would use some sort of IF to invoke the DAX BLANK function for that row, however. By The Way, if you download the Excel file for this video, I have created a DAX Solution for this "Get Previous Row" problem : ) Thanks you for your support on each video, J. Obando, with your comments, thumbs up and of course your Sub : )
Thanks for the quick response, even on Saturday :-O. Always thumns up 👍🏻 for your videos, and subscribed since 2012... It’s been a long time learning from you. Best regards from Colombia 🇨🇴
Yes, from the data set in the video: = IF ( MIN ( GEPrices[Date] ) = GEPrices[Date], BLANK (), GEPrices[GE Close] - LOOKUPVALUE ( GEPrices[GE Close], GEPrices[Date], MAXX ( FILTER ( ALL ( GEPrices[Date] ), GEPrices[Date] < EARLIER ( GEPrices[Date] ) ), GEPrices[Date] ) ) ) Also, if you download the Excel Workbook and open the Data Model, I have this there.
There is an easier way to do that. Create two index columns, one starting with 0 there with 1. After that merge the table with itself matching the two different index columns. Because they are off by 1 the results will be staggered. Then clean the excess data and presto!
Is it really easier? A few viewers said it was easier and a few said the lookup was easier. Is it fewer steps? Is it more efficient in performance? There seems to be a debate about this... I guess we will have to test : )
I really like this method, but for some reason it makes it incredibly slow to load it into my data model, even though I'm doing these steps in the staging query. Any ideas? Thanks.
Yes I am also facing the same problem. No wonder this method is easy and well explained. But its not working with large data - taking endless time to load the data
Hi Mike, sorry for the random question, but would you be able to recommend a couple of books that are good for learning about developing financial models in excel. Both intermediate to advanced level. This includes building models for both corporate finance and project finance type businesses. Many thanks,
I am not sure. I do have a corporate finance playlist. Have you seen it? I made it 8 years ago - 110 videos - it shows every possible cash flow calculation and Excel Financial Function. Here it is: ua-cam.com/play/PL90E1F26C7B85E78F.html
Unable to find the download link, which says --- Entire page with all Excel Files for All Videos: If possible do the correction, and also let me know. Thanks
Holle Mike, i am trying to get something similar. I use very bad raw data that comes with order number in a column and all items below but the order is just at the top I had a VBA Routine that copied down the order num until finds the next order number and so on. I want to do the same in PQ adding a column but I can't reference the value up in the same column. The new column is odv and the order title is column4. This is what I tryed. New column name is ODV:. if [column4] = null then #"indice agregado" {[Índice]} [ODV ] else [Column 4]. Did not worked of course. Any ideas?
I am not sure I understand. Can you send a small, but logically parallel example in an Excel file with: 1) what the data looks like at start and 2) what you want the end result to look like to my email: excelisfun at gmail
Mike, this is by FAR the most elegant M-Code pattern I've seen for this solving this problem in PQ. Well done!
Glad it is elegantly helpful, Randy! What other ways have you seen?
@@excelisfun Other ways? Self-Merging after adding index columns twice (from 0 and from 1) :-))
By the way....I was probably one of the first to show this technique... five years ago ;-)))
It is nice to see that it is useful....and perfectly explained :-)))
Thanks Mike!!!!
@@BillSzysz1 , Just like at the Mr Excel Message Board and formula solutions, people say; "It all starts with Aladin", the same is true about PQ: "It all starts with Bill Szysz"!!!! : ) Thanks for leading the way, O Master Poet and Artist of PQ, Bill Szysz!!!
Congrats, after looking 5 videos, but this is so far the best elegant and straight to the point of what I needed!!!!.
You have my like to your video!!! (y)
Two way lookup is the key to fix the puzzle and plus realize that importance of index thanks for sharing
You are welcome!
Great explanation! I used to do the same with Merge operation. I used to add two Index columns (first starting from 0, the sesecond starting from 1) and then I merged the table with itself selecting first index column and second index column to get the offset :)
The solution you provided is much more elegant :)
Thanks, Teammate!
That's the way I would approach it as well. Less complex.
@@jasonm8098 Which way?
@@excelisfun The Merge with itself pmsocho mentioned.
@@jasonm8098 Got it : )
Excellent. It's so simple when explained so well. Thanks Mike.
You are welcome, Matt!!
Exactly what I needed, and I love how you broke it down and explained all the steps. Thank you :)
Glad to help!
Another excellent video to build on the MSPTDA 09 M Code tutorial. It is an excellent foundation for doing IF functions and LOOKUP in Power Query. The video had a great explanation of Positional Index, Field Access, and Lookup operators.
Today I applied this query, fantastic Mike
Mike, I've been using PQ for the majority of a solution I'm building but have had to bring the data back into Excel to add a formula to determine if inventory is being shipped in FIFO. I can now stay in PQ and determine based on the previous row and a few other variables if a shipping line was shipped in order! I've learned so much from you and I really appreciate you so much! Thanks for all you do for us!
You are welcome, Nancy!! Thanks for your support with your comment, thumbs up and your sub : )
You explain this very well
You are a true genius
Just having fun with Excel : )
You are the man, Mike! By far the best PT content out there
Amazing. I‘m needed this formula !!!! Thanks man!!!
You are welcome man : )
Absolutely amazing! Thanks so much !!!
Umbelievable, i needed this M code a few weeks ago for a dataset containing a cumulative sales and i have to figure out monthly for every day in the month the amount of revenus. A 10000000000 thumbs up to you mike :-)
Thank you for the 10000000000 thumbs up, Mohamed!!!! : )
That is really illuminating. I've been trying to something similar to your looking up previous but instead with averages, maximums and minimums. It's difficult.
It is different than Excel, that is for sure : )
I'm telling you....this is TopGun! Wow!!
I needed to create my own text file and adjust the Source step for the Queries to work but that was additional practice with Power Query! Thanks for the interesting technique!
O.... I should have posted the Text file too. Tomorrow when I get back to work...
Here is the text file: people.highline.edu/mgirvin/UA-camExcelIsFun/GEStockPricesEMT1546.txt
@@excelisfun Thanks. What I like really about your videos is that I can follow along to learn instead of just passively watching!
Thank you so much. Excellent teacher and very well explained.
You are welcome so much, Khaled!!!
Mike; you made is so simple to use previous row data; Wonderful explanation. Thanks for sharing your knowledge and experience.
Glad you like it and it is simple for you, Sanjeev!!!
Thank you been struggling for a week with this and explained so well. Love your videos never stop!!
Glad to help : )
Wow Mike.. that's some spicy lookup magic using M Code in Power Query. You always push to and beyond the boundaries in Excel. Excellent and thumbs up!
Thanks , Wayne! Glad it is cool for you : )
Mike, excellent video! Really wish you show us more of this PQ magic ! Any chance you may record a more advanced version of "MSPTDA 09" that builds on the concepts explained there? I ask as the powers of PQ seem to be endless every time I browse the PQ forums. But I only seem to understand the way you explain those tough hard to understand concepts.
As time goes on, yes, I will post more!
SO this is awesome! I was grappling with a similar problem and I thought "I bet Mike has a video on this..." Voila!
Voila!!! ; )
Great way to calc stock price change! Thanks!
Glad I can help, Armond : ) : )
The -1 index is what I was missing until now ;). Thanks Mike!
You are welcome for the -1, Cristain!!!
This is absolutely excellent! Best I've ever seen. Thank you!
best way to start the day! coffee and amazing PQ tricks by Mr. Mike!
Coffee and PQ fun!!! I love it : )
Wow!! your a Genius my friend!!
Thank you for your videoes, they have been such a great help in terms of learning Power Query/BI!
You've briefly touched upon each and when to replace it with _ in your earlier videoes. I'm still struggling a bit with understanding the aspect of each so I was wondering if you could elaborate on why you did not do it in this formula?
Is there maybe some sort of easy rule of thumb I could follow?
Thank you Mike, you do make all easy and fun in excel.
Glad it is easy and fun for you - for me making a video all comes down to one thing: how can I tell a story that can reveal difficult concepts in a way that is not difficult : ) Thank you for your support on each video that you watch with your comments, Thumbs Ups and, of course, Katerina, your Sub : )
As always well explained.
Glad you like it and it helps, David! Thanks for your support on each vid with those comments, thumbs up and Sub : )
Mike, your way of explaining makes it so clear and easy.
Thanks a lot. By the way, I found there is Intellisense in M code editor in Power BI, but not available in Excel. Would have helped a lot...
Yes, that would be cool if it was in Excel : )
Wow, this is like a dream! Never thought this is possible in PQ!
Dreams are good, Jun Xia : ) I think: anything is possible in Excel or PQ or DAX... : ) : )
Great video!!
Neat and clean! Thanks!
Glad it helped, Leandro!!!
That's very nice solution! I wish syntax of the same functions was the same in different platforms
Glad it is nice, Vida!!! But yes, Excel, Power Query, DAx and others are all very different!!
Amazing!!! Thank you
Thank you so much. You saved me many hours!! :)
Very good explanation
This is so cool!
I used to achieve this by adding two index columns, one starts with 0 and the other one starts with 1. Then merge the table with the table by itself using the two index columns as matching columns. Then you know, I extract the value I need. I have (edit: not) tried, but I believe your way should be more efficient in terms of performance when we are during with a big table.
Thanks for sharing this, Mike
You are welcome for the share, MF Wong!!! I have not tested performance, but the lookup method seems a bit easier : ) Thanks for your support : )
Great technique. Can come handy in some situations.
Glad it helps, Sal A!!!! Handy is good, just like your support is good : )
I recently did something similar to get the last exchange rate of the last available day of the month.
My code was not as clean as yours and now I have to reimprove it.
Cheers.
Glad the videp helps, nlz90! Thanks for your support : )
You are magic. Thanks.
You are welcome, Bilal!!!
Perfectly explained! Thanks Mike!
Thanks, Kevin, glad you liked it : )
Great stuff thank you!
Glad it helped!
need to get more familiarized with M code. looks tough, but i probably felt the same when i first learned vba
Yes, it is like anything, we just have to learn it : )
FASCINATING!!!!! WOW!
PQ fun, Oz : )
Many thanks sir !!!
Most welcome!
Thank you Mike :)
You are welcome, Filip!
Excellent 👍
One more amazing video.
Great video Mike !! M-Code insights are awsome.....
Glad it is insightful for you, Victor!!!
well done!
Thanks, Steven Nye!!!!
Thank you
Long live Mike. Thanks for the share
You are welcome for the share, Syed : )
You're a genius. I'm looking forward for a German remake ;-)
... I am sorry teammate, I do not know German... : (
Very nice and well explained.
I used to add two index columns, starting from 0 and from 1 a d then merge the query with itself
But this solution is much better
Thanks for the method you used to do. I had not done it that way. But, yes, most times doing a lookup will be easier. Thanks for your support, Kolyu!!!
Could always use isnumber instead of ROWS to sort out that first row problem. Thanks as always Mike
I do not know how to use ISNUMBER in that situation. How do you do it, Dave ?
@@excelisfun =IF(ISNUMBER(B1),B2-B1,"") or even =IF(ISText(B1),"",B2-B1)
@@davebowman5392 O, of course!!!! Thanks, teammate : )
Great!!!
As always
Great explanation!
Glad you like the explain, Tobias!!!
Thank you for sharing Mike
You are welcome for the share, Mehdi!!!!
Great. Thanks man"!
You are welcome, Fabian!!!!
I spend a lot of time watching your videos Mike...they are all excellent, thank you !! Glad you did a query for previous row, as I've been looking for a solution like this for some time and you have explained it brilliantly. I suppose it could be adjusted to select a cell reference from 2 rows before or more?
Glad you get to watch a bunch of vids and they are EXCELlent for you, Paul! Thanks for your support on each video that you watch with your comments, thumbs ups and of course your Sub : ) Yes, you can easily get two above also ; )
@@excelisfun Thanks, Mike for creating this video. How would you reference a cell from 2 rows before or more?
This is great, how can I get the index column to start over with -1 with each day?
By the way, I didn't tell you that I am having a great time with the new calc engine :) :) this is so GREAT!!!!
Yes!!! Love to hear it, John : )
I’s all in the index :-). Super great trick right there!
Once you know how to do it, it’s not that difficult. Thank you for sharing!
Yes, indeed, the truth about everything, Geert: Once you know how to do it, it’s not that difficult. It is just how to get something totally new into out head in the first place to know it : )
And there’s more: how to keep it in our heads (=> repeat and practice) - for my current project I’m rewatching MSPTDA, I’ve refinished the PQ part, now I’m at the DAX stage again (that context transition and CALCULATE are fantastic for aggregation - so simple, so I promoted that calculation from PQ to PP)...
Now there is contrast: your Crazy Array Formulas (TM_MikeGirvin) remain difficult no matter how you slice it (no pun intended).
Anyway, it’s back to work for me. It’s nice to drop you a line now and then. :-)
@@GeertDelmulle Yes, sir! I am so happy to hear that you are re-watching and refining your knowledge. Guess what? Me too! I created most of MSPTDA last year, and now that I am teaching this class at Highline (this is second week) I had to re-watch MSPTDA to remind me of the story and how students will see it, so that I could create new data sets and graded assignment. What calculation did you have in PQ that you promoted to DAx, and why? Always great to hang out on the Team with you, Geert : )
Mike,
The calculation that got promoted from PQ to DAX: aggregation of a max(some date field) from the reporting periods sub-level to the project level (higher level/lower granularity). Reason: much simpler in DAX:
- in PQ I had to create an extra query that aggregated using Group By and List.Max, then had to merge that query back into the fProject-table/query. But I was not allowed to merge it back into the orig table, I had to go for the option of a new query. Rather ‘indirect’ and complicated. Don’t worry: it worked.
- In DAX: calculated column in the fProject-table: := CALCULATE(MAX(fPeriod[Some Date])), and... DONE! (both tables are related). The most efficient aggregation I’ve ever seen (cfr. MSPTDA15 -I think-, where you do the same using SUM).
Much simpler, and more efficient. And since I learned from a certain Mike Girvin that you should always go for the most efficient solution, that’s what I did. (the thing I’n building will be used for a long time and for an ever increasing set of data).
Now don’t get me wrong: I love PQ to bits, but the sheer power and efficiency of DAX is amazing. Now that I’ve arrived in the DAX stage of the development of my application, I deepen my knowledge and understanding of DAX in my quest to make it as efficient as possible.
I rewatched most of the DAX videos today (excl. PBI), but the one on CALCULATE is still on the list: I know for sure that I need the knowledge in that video for my application. In some video by Mr.Excel I saw recently, I heard Bill call CALCULATE to be some sort of SUMIFS (but then much more generalized). I find that an interesting comparison.
Good luck with your new class at Highline, and have fun! Your students are so lucky to have such an amazing teacher!
@@GeertDelmulle He said it was like SUMIFS cuz Rob Collie wrote that is his book. That is not really a good parallel. It changes Filter Context, in o so many ways!!! The CALCULATE videos is intense and very dense with many examples of changing the Filter Context, and also the potential pitfalls of CALCULATE and DAX... I agree with you, some DAX formulas are just so simple to create and do complicated things : )
Sir, pls make video for adding index column to sub group in power query and getting previous record to arrive how stock perform for various days.
I got close price of multiple Days for multiple stocks. Thanx
Gr8 trick Mike but here simplicity of Excel is clear-cut winner over complexity of Power Query. I was looking for this trick.
Yes indeed, the freedom in an Excel Worksheet is unmatched. But Power Query and DAX both have certain tools that make certain tasks much easier, but not his one : )
Thanks Mike as always :)
You are welcome as always, John Borg : )
Super video Mike
Glad you like it!
@@excelisfun
I have one tricky problem in dax table.
Would you help me out?
8:11 mr. Excelisfun, why not use Table.skip function to skip one row in the added table and then simply subtract values and absolute them? would that be easier?
Thanks 👍
You are welcome, Manar!!!
PQ rules!
Suggested Sub-Title: How to take something easy and make it a bit hard!! An excellent reminder that PQ adds to the Transformation Took Kit but does not replace existing. Assuming that the data set is not coming from excel (reasonable thought) and that the ultimate goal is to use the Data Model, my temptation would be to leave the PQ Purgatory ASAP and finish this transformation in DAX!?! For me DAXMeasureFunFactor>M-CodeFunFactor.
But... The DAX Formula is even harder...
=IF(MIN(GEPrices[Date])=GEPrices[Date],BLANK(),GEPrices[GE Close]-LOOKUPVALUE(GEPrices[GE Close],GEPrices[Date],MAXX(FILTER(ALL(GEPrices[Date]),GEPrices[Date]
"Harder" is definitely an individual judgement. DAX formula is LONG, but mostly due to referent syntax (like table nomenclature formulas in Spreadsheet Excel). The structure is to me very straight forward and the formula comes together logically and quickly. So personally, I will take DAX any day. BUT I understand that is not a universally shared view!! The "problem" With DAX: just so many (powerful & useful) functions to learn!! The number is large and the growth is exponential. But isn't that part of the FUN? :) Just wish that any new DAX available in PBID would be simultaneously available in EXCEL (DAX). Why can't we have EVERYTHING? LOL
@@richardhay645 I agree; Fun! DAX is fun : ) PD is fun : ) Excel is fun : ) Go Team!!!!
Very good video! Although, I couldn't make it work in my case. I have a table of tables, and I need to calculate the moving range for each individual table. My main table has two columns, first with the group name, second the table related to it. In each of these tables I would like to include this code, but once I try to use this method, or it complains that my "AddedIndex" is not recognized or in the best case scenario, when using [AddedIndex][Index] I managed to get the list of the indexes... It doesn't work with {} also. Instead of each I had to use (r)=> for it to recognize my nested table...
Nice Nice nice. Thank You. I always wanted to know that.
You are welcome! I am glad that it helps! Thank you, Felipe, for your support with your comment, thumb up and your Sub : )
Awesome 👏 😎
Glad it is awesome for you, fshaikh!!!!
@@excelisfun it is no doubt about.. I need find who are the one with dislike for such a great content..
@@ExcelExciting Thank you very much for your support... Yes, I do not know why people click Thumbs Down: maybe they have confused their right to free speech with their duty to try and make the world a better place?
This is great. I have a question though along the same lines but slightly different. Can column reference itself in the formula. for example:Table.AddColumn(AddedIndex, "Open Balance", each if [Date] = [LOAN.Start Date] then [LOAN.Amount] else AddedIndex{[Index]-1}[Open Balance]) I'm trying to use Excel Power Query to develop loan forecasting
Hi. Thank you so much for your initiative. Due to your effort , I have been learning power query.
I have a big problem and I need your help, Though I had researched, I couldn't find answer.
I have a dimension table with 5 thousand rows Table A. This table contains 3 columns.
I also have a table (Table B) with 3 millions rows.
I need to connect both tables, but I can't use a lookup with power query due to my dimension have a range, I need looking for a row based in a range.
Eg: colunm 1= 1000 , column 2 = 2000.
How can I select a related row if my fact contain a record = 1550 ? Is that possible use a lookup on that case?
Can you help me?
Is there a video that can show me how to create a report from a linked spreadsheet? At the moment anytime I refresh the data the new info I’ve inputted goes out of whack. Help please!!!!
As always, excellent video with very helpful information, thank you very much. I have a question: supose we want to load the final query into the Data Model. I think Power Pivot would identify the last column as text because of the NULL, what would be the workaround for that?
A column with null and numbers will work find in the Data Model, for example if you added 1 to the column in a DAX Calculated Column, it would work fine. Probably, you would use some sort of IF to invoke the DAX BLANK function for that row, however. By The Way, if you download the Excel file for this video, I have created a DAX Solution for this "Get Previous Row" problem : ) Thanks you for your support on each video, J. Obando, with your comments, thumbs up and of course your Sub : )
Thanks for the quick response, even on Saturday :-O. Always thumns up 👍🏻 for your videos, and subscribed since 2012... It’s been a long time learning from you. Best regards from Colombia 🇨🇴
@@JorgeObando Thank you for your long time support , J. Obando!!
good
OMG!!!
I agree! OMG, so much fun!!!!
I love You.
I love you too, and the rest of our Excel Teammates! That is why I post : )
Ingenious MIke :)
Glad you like it, nimrodzik1!!!!!
Thanks allot Sir. Is there any in Dax!
Yes, from the data set in the video:
=
IF (
MIN ( GEPrices[Date] ) = GEPrices[Date],
BLANK (),
GEPrices[GE Close]
- LOOKUPVALUE (
GEPrices[GE Close],
GEPrices[Date], MAXX (
FILTER ( ALL ( GEPrices[Date] ), GEPrices[Date] < EARLIER ( GEPrices[Date] ) ),
GEPrices[Date]
)
)
)
Also, if you download the Excel Workbook and open the Data Model, I have this there.
How can I find different values in a list a multiply by previous 12 rows?. Thank you
What if you have different variables in evry date?
There is an easier way to do that. Create two index columns, one starting with 0 there with 1. After that merge the table with itself matching the two different index columns. Because they are off by 1 the results will be staggered. Then clean the excess data and presto!
Is it really easier? A few viewers said it was easier and a few said the lookup was easier. Is it fewer steps? Is it more efficient in performance? There seems to be a debate about this... I guess we will have to test : )
@@excelisfun Looking forward to the results. Thank you very much for the great videos!
Nice, but I will still do it outside PQ
Me too, unless I needed it in a data model transformation : )
Wow
I really like this method, but for some reason it makes it incredibly slow to load it into my data model, even though I'm doing these steps in the staging query. Any ideas? Thanks.
Yes I am also facing the same problem. No wonder this method is easy and well explained. But its not working with large data - taking endless time to load the data
Couldn't you just add two index columns, one at 0 and one at 1, then do a self join on the index columns?
Hi Mike, sorry for the random question, but would you be able to recommend a couple of books that are good for learning about developing financial models in excel. Both intermediate to advanced level. This includes building models for both corporate finance and project finance type businesses. Many thanks,
I am not sure. I do have a corporate finance playlist. Have you seen it? I made it 8 years ago - 110 videos - it shows every possible cash flow calculation and Excel Financial Function. Here it is: ua-cam.com/play/PL90E1F26C7B85E78F.html
@@excelisfunMany thanks Mike, will have a look at it now :-)
Unable to find the download link, which says --- Entire page with all Excel Files for All Videos: If possible do the correction, and also let me know. Thanks
Top file is active and server is working
I'm trying this on a table with 240,000 rows and it's taking a long time. Is there anything that can speed it up?
Holle Mike, i am trying to get something similar. I use very bad raw data that comes with order number in a column and all items below but the order is just at the top I had a VBA Routine that copied down the order num until finds the next order number and so on. I want to do the same in PQ adding a column but I can't reference the value up in the same column. The new column is odv and the order title is column4. This is what I tryed. New column name is ODV:. if [column4] = null then #"indice agregado" {[Índice]} [ODV ] else [Column 4]. Did not worked of course. Any ideas?
I think maybe creating a variable if is possible
I am not sure I understand. Can you send a small, but logically parallel example in an Excel file with: 1) what the data looks like at start and 2) what you want the end result to look like to my email: excelisfun at gmail
Great
Glad it is great, ghislain!!!
The GEStockPricesEMT1546.txt file is not available for download.
Here is the text file: people.highline.edu/mgirvin/UA-camExcelIsFun/GEStockPricesEMT1546.txt
@@excelisfun Thank you Very much and sorry for the inconvenience…