@@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!!!
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 :)
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)
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!
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.
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.
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 :-)
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.
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!
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?
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...
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 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 : )
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
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 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 ; )
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?
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 : )
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...
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 : )
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!!!!
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 : )
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 🇨🇴
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: ruclips.net/p/PL90E1F26C7B85E78F
@@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?
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
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.
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
It does take a while to learn, but once you have it, it can do some things that Excel can not do very easily, and so it is good to have PQ in our Excel tool bag!
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!!!
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 : )
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!
Exactly what I needed, and I love how you broke it down and explained all the steps. Thank you :)
Glad to help!
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 : )
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.
Excellent. It's so simple when explained so well. Thanks Mike.
You are welcome, Matt!!
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 : )
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!!!! : )
You are the man, Mike! By far the best PT content out there
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!
You explain this very well
Today I applied this query, fantastic Mike
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/RUclipsExcelIsFun/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 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; 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!!!
Amazing. I‘m needed this formula !!!! Thanks man!!!
You are welcome man : )
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?
The -1 index is what I was missing until now ;). Thanks Mike!
You are welcome for the -1, Cristain!!!
Absolutely amazing! Thanks so much !!!
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 : ) : )
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 : )
I'm telling you....this is TopGun! Wow!!
Thank you so much. Excellent teacher and very well explained.
You are welcome so much, Khaled!!!
You are a true genius
Just having fun with Excel : )
best way to start the day! coffee and amazing PQ tricks by Mr. Mike!
Coffee and PQ fun!!! I love it : )
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 : )
This is absolutely excellent! Best I've ever seen. Thank you!
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 : )
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 : )
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!!
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 : )
Neat and clean! Thanks!
Glad it helped, Leandro!!!
Great video!!
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... : ) : )
Thank you so much. You saved me many hours!! :)
Amazing!!! Thank you
Wow!! your a Genius my friend!!
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 : )
Great technique. Can come handy in some situations.
Glad it helps, Sal A!!!! Handy is good, just like your support is good : )
Perfectly explained! Thanks Mike!
Thanks, Kevin, glad you liked it : )
Very good explanation
Great stuff thank you!
Glad it helped!
You are magic. Thanks.
You are welcome, Bilal!!!
This is great, how can I get the index column to start over with -1 with each day?
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 : )
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 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 ; )
@@excelisfun Thanks, Mike for creating this video. How would you reference a cell from 2 rows before or more?
Thank you Mike :)
You are welcome, Filip!
You're a genius. I'm looking forward for a German remake ;-)
... I am sorry teammate, I do not know German... : (
One more amazing video.
Many thanks sir !!!
Most welcome!
Excellent 👍
Great video Mike !! M-Code insights are awsome.....
Glad it is insightful for you, Victor!!!
FASCINATING!!!!! WOW!
PQ fun, Oz : )
well done!
Thanks, Steven Nye!!!!
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?
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 : )
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 : )
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...
Thank you
Great. Thanks man"!
You are welcome, Fabian!!!!
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 : )
Long live Mike. Thanks for the share
You are welcome for the share, Syed : )
As always
Great explanation!
Glad you like the explain, Tobias!!!
Great!!!
Thank you for sharing Mike
You are welcome for the share, Mehdi!!!!
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!!!!
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!
PQ rules!
Super video Mike
Glad you like it!
@@excelisfun
I have one tricky problem in dax table.
Would you help me out?
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!!
Thanks Mike as always :)
You are welcome as always, John Borg : )
How can I find different values in a list a multiply by previous 12 rows?. Thank you
Thanks 👍
You are welcome, Manar!!!
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 : )
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: ruclips.net/p/PL90E1F26C7B85E78F
@@excelisfunMany thanks Mike, will have a look at it now :-)
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?
I love You.
I love you too, and the rest of our Excel Teammates! That is why I post : )
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
good
What if you have different variables in evry date?
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.
Couldn't you just add two index columns, one at 0 and one at 1, then do a self join on the index columns?
Ingenious MIke :)
Glad you like it, nimrodzik1!!!!!
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?
OMG!!!
I agree! OMG, so much fun!!!!
Nice, but I will still do it outside PQ
Me too, unless I needed it in a data model transformation : )
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
Wow
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!!!
power Query Tough for me.Excel is Much Easier.
It does take a while to learn, but once you have it, it can do some things that Excel can not do very easily, and so it is good to have PQ in our Excel tool bag!