Power Query - Index Column by Group & Return Last and First Record for a Group

In this video I show how you can create a index Column for a group (Partition by) and return the last and first records for a Grouped Table.
using a email dataset grouped by Email Subject. #data #dataanalytics #powerquery #powerbi #excel #partition #mquery #tutorial
Links
File
github.com/jbo...
Video on Each and Underscore
• Power Query - Understa...
Mquery functions used
add index column
learn.microsof...
table last
learn.microsof...
table select rows
learn.microsof...
table addcolumns
learn.microsof...

[@johnjaftha6770]

this is brilliant! i was trying to lookup devices which are sold and then returned. in excel you can lookup to first value - but this is much more elegant

[@Theomatikalli]

Thanks for the vid. Though I managed to replicate your soln, I don't understand the thought process that went into it i.e. why you replace a particular underscore with the index function and why you replace the table in the index formula with an underscore. Why are all those particular changes are okay. In essence I feel the reasoning of why certain actions were taken can be more empowering as it enables the users to think of other situations where similar reasoning can be applied. I would have also loved to understand those Power Query architectural decisions i.e. generalizations.

[@basensei8699]

awesome ! I've made a video on the use of the underscore kzbin.info/www/bejne/jam6fWafZZqbkJI this might be able to help. :)

[@zorankrekic3006]

Nice, I had similar problem last year, and my solution used 3 queries, 1. get data, 2. get oldest and 3. get youngest, after merge 2 and 3 query

[@SndfOmar]

Cool dude! Thanks for sharing.

[@SamehRSameh]

I need m-code to return true if column contain only specific value And return false if contains this value and other value

[@QasimAli-gy3nb]

What tool are you using for recording these amazing videos, please.

[@basensei8699]

Camtasia ;)

[@QasimAli-gy3nb]

@@basensei8699 Oh that's very pricey.