Hi David. Just found your channel. Awesome example! For the simple scenario, the merge query step throws the top row to the bottom, so you have to remember to sort by the 0 Index column before close and load. Another method I employ is to bypass the query merge step. Instead, add only the 0 Index column and then add a custom column with the following (using your columns as example) = try Source[#"Tests (cummulative)"]{[Index]-1} otherwise null. This will produce the desired column with reference to the prior row value with null at the top. Also, you don't have to sort. I'm sure you know this method. Just posting it for others if interested. Of course, wouldn't work for your complex example. Thanks for sharing your videos. Subscribed and looking forward to more. Thumbs up!!
Great content. To shift rows with Index0 Index1, simple and genius. Will be really helpful at hunting irregularities in time series data. One more block for proper data validation by PQ. On the second part of the video, sure, I should dig more, the treasure ahead. Thanks a lot, David.
I loved the trick, but I also loved how in a subtle way you invited people to follow your content. More people on youtube should do it similarly I think
Very good David. Also the small tips: like the way you rename the merged columns. Excellent. By the way: you can somehow fill down in regular Excel: just select the range, F5 go to special, blanks and refer to the top cell, press ctrl-enter.....
Hi David, that was the best video for referring to previous/next rows I have come across. Thanks for sharing, this was very useful. Would be great if I can get the file to follow along with the video. 😊
Hi yes glad you like it, I use this trick like all the time! Ah I don’t think I still have the file but maybe email me and I can see what I can do david@xlconsulting-asia.com
This was exactly what I was looking for so thanks for the video David, but what if you don't have every single dates on your data, if there are only workdays or some reason a date could be missing - how would you create that custom column for "yesterday" which could be last Friday if you don't have weekends in your data?
Thank you for the video. I am trying to classify something based on a previous row. Can that be achieved. So if C3="Green", then B2="the previous record is green"
@@learnspreadsheets After having two columns, i am trying to make a third column produced that makes one value of the column supercede the second column. Can that also be achieved
Will look at the 2nd option more closely. I have "check in" and "check out" on individual rows. I can identify them by person Id. Unfortunately not every "check in" event is followed by "check out". Any tips how to get adjecent "check in & out"-pairs of same person spotted correctly, disregard events where is only in OR out stamp? Direct indexing doesn't work as it will - in case of missing in OR out stamp - merge to rest of a single person id incorrect check type to row
Hi David. Just found your channel. Awesome example! For the simple scenario, the merge query step throws the top row to the bottom, so you have to remember to sort by the 0 Index column before close and load. Another method I employ is to bypass the query merge step. Instead, add only the 0 Index column and then add a custom column with the following (using your columns as example) = try Source[#"Tests (cummulative)"]{[Index]-1} otherwise null. This will produce the desired column with reference to the prior row value with null at the top. Also, you don't have to sort. I'm sure you know this method. Just posting it for others if interested. Of course, wouldn't work for your complex example. Thanks for sharing your videos. Subscribed and looking forward to more. Thumbs up!!
Thanks Wayne! Yes that’s a great suggestion using custom code, I’ll have to try it myself!
@@learnspreadsheets Awesome! Cheers :))
Great content.
To shift rows with Index0 Index1, simple and genius.
Will be really helpful at hunting irregularities in time series data. One more block for proper data validation by PQ.
On the second part of the video, sure, I should dig more, the treasure ahead.
Thanks a lot, David.
Thanks yeah! Such a useful technique eh?
I loved the trick, but I also loved how in a subtle way you invited people to follow your content. More people on youtube should do it similarly I think
Thanks! I’m glad you found it useful 🙃
Fantastic! This is such a clear explanation and opens up a while new set of possibilities in power query!
Yes!! Love the possibilities this feature can unlock 🙃
This is by far the best and simplest of many videos to understand...Many many thanks..😊
That's very kind! I worked hard on it, so I'm happy you found it useful! I just published a new short Power Query video now!
Very good David. Also the small tips: like the way you rename the merged columns. Excellent. By the way: you can somehow fill down in regular Excel: just select the range, F5 go to special, blanks and refer to the top cell, press ctrl-enter.....
Thanks Bart! Glad you like it. Yes I know the excel fill down train too 😃
Wow, I didn't knew you could merge queries using the same query itself, thank you!!!
Yessss! It’s not so intuitive but it’s great!
David, thanks a lot!! Fantastic!!
Glad you like it!
Hi David, that was the best video for referring to previous/next rows I have come across. Thanks for sharing, this was very useful. Would be great if I can get the file to follow along with the video. 😊
Hi yes glad you like it, I use this trick like all the time! Ah I don’t think I still have the file but maybe email me and I can see what I can do david@xlconsulting-asia.com
🤠Thank you for just an easy method!!!
Yes! Love the simple no code methods!
Now I understand the purpose of selfjoin, cool!
Yeah there’s a few random use cases like this!
Great tricks, David! Very useful!
Thanks! Glad you liked it
This was exactly what I was looking for so thanks for the video David, but what if you don't have every single dates on your data, if there are only workdays or some reason a date could be missing - how would you create that custom column for "yesterday" which could be last Friday if you don't have weekends in your data?
Heya! Thanks for the feedback, glad you liked it, that would get a bit trickier but here is a forum question on it
Great video David!!! How about if the dates are NOT consecutive?
Hmm I would say as long as dates are sorted the same idea could work
Thank you for the video. I am trying to classify something based on a previous row. Can that be achieved. So if C3="Green", then B2="the previous record is green"
Yea that works, use the trick i shiw in the video to get the previous row in the current one then add a conditional column with the criteria you need
@@learnspreadsheets After having two columns, i am trying to make a third column produced that makes one value of the column supercede the second column. Can that also be achieved
Hi david loving your videos! Is it possible to get a currency conversion spreadsheet? Thank you
Hi please email me on david@xlconsulting-asia.com
Thank you so much
Glad you like it!
Will look at the 2nd option more closely. I have "check in" and "check out" on individual rows. I can identify them by person Id. Unfortunately not every "check in" event is followed by "check out". Any tips how to get adjecent "check in & out"-pairs of same person spotted correctly, disregard events where is only in OR out stamp? Direct indexing doesn't work as it will - in case of missing in OR out stamp - merge to rest of a single person id incorrect check type to row
Hmm that sounds more complex would need to think about it but I think you’re on the right lines and should eventually figure out a way
hi how we can refer the row in the custom colum formula
Hi the video explains various methods, it’s not as easy as it should be though!