Power Query - Get Top N by Group

[@flaviogarlatticosta]

Thank you for this wonderful tutorial, English is not my native language however I learned many things from this very well done m code and with a logic never seen until now. Good work.

[@bcti-bcti]

Thank you for taking the time to watch and put up with my accent. I hope it wasn't too irritating 😄

[@flaviogarlatticosta]

@@bcti-bcti Thank you, for the “Charming Tennessee accent” (I guess) 🙂

[@janesheng7113]

This video has been incredibly helpful! I was smiling all the way through as I watched. Thank you!

[@bcti-bcti]

@@janesheng7113 Im so glad you found it so enjoyable. Thanks for watching.

[@petercompton538]

This is excellent - very useful and very well explained! Thanks

[@bcti-bcti]

@@petercompton538 thanks for watching!!!!!

[@Luciano_mp]

Great work, awesome! Thank you very much!

[@bcti-bcti]

Thank you for saying so. And thanks for taking the time to watch.

[@IvanCortinas_ES]

A very good tutorial. Thank you for this great job!!!

[@bcti-bcti]

@@IvanCortinas_ES thanks for watching!!!

[@kebincui]

great as always ❤👍🏻

[@bcti-bcti]

@@kebincui thank you!!!!

[@jerrydellasala7643]

Great video & technique (and thanks for posting the file!). I ran into one small issue with the Parameters SOLUTION file. When I had more rows in the report, the columns were not retaining the formatting. I'm on the Beta and maybe it's a quirk there because when I checked the Properties of the table, you had Adjust column width unchecked which is fine, and you have Preserve cell formatting CHECKED which would make one think I shouldn't have had that issue. Luckily I found that checking Preserve column sort/filter/layout fixed the problem. As I said, it might be a Beta issue. It might have been helpful to point out that the list of Group By columns could NOT be included in the final report. Anyway, still great lesson!

[@bcti-bcti]

Yeah, I encountered that same issue. I found it quite irritating. Thanks for your observations and input.

[@willbedeadsoon]

Like before watch, 'cause I know this channel as a top-class soure of information!

[@bcti-bcti]

This was published accidentally. It will be back up CORRECTLY in a few hours. Sorry for the confusion.

[@bcti-bcti]

@@willbedeadsoon all fixed. Thanks for being patient.

[@excel_along_the_way]

Thank you for an excellent tutorial, I must really check out again that parameter video, Next video, getting the same results with excel formulas, like a single cell report,

[@bcti-bcti]

I'm unclear about what you are requesting for the next video. Thanks.

[@excel_along_the_way]

@@bcti-bcti I see that this seems only possible with PQ just ignore my request.

[@flyingHubby]

Great video. Thanks. but I'm missing the link to the example files.

[@bcti-bcti]

This was published accidentally. It will be back up CORRECTLY in a few hours. Sorry for the confusion.

[@bcti-bcti]

@@flyingHubby all fixed. Thanks for being patient.

[@billgillaspey9036]

Q: why did the order of the columns change from 13:45 to 14:05 where columns are being selected...?

[@bcti-bcti]

It's not that the columns changed, it's that after the data was extracted, the table was scrolled to the right. The other columns (like "Sales Representative", "Date", "Product", and "State") are off-screen to the left. Thanks for watching and the question.

[@jerrydellasala7643]

You forgot the sample file!

[@bcti-bcti]

This was published accidentally. It will be back up CORRECTLY in a few hours. Sorry for the confusion.

[@bcti-bcti]

@@jerrydellasala7643 all fixed. Thanks for being patient.

[@alvinzang3738]

Using Table.MaxN would be much easier to get the same result.

[@bcti-bcti]

Thanks for the suggestion. Can you provide a sample of M code to demonstrate your technique? Thanks.

[@alvinzang3738]

@@bcti-bcti Yes, of course. The general idea remains the same, with a slight improvement. I might write the core part of the code like this: let Source = Excel.CurrentWorkbook(){[Name = "SalesData"]}[Content], // Retrieve parameters from the parameter table itemCount = Parameters[Item Count]{0}, colToGroup = Parameters[Group By]{0}, // fnToUse = {Table.MinN, Table.MaxN}{Number.From(Parameters[TopBottom]{0} = "Top")}, fnToUse = if Parameters[TopBottom]{0} = "Top" then Table.MaxN else Table.MinN, Grouped = Table.Group( Source, colToGroup, { "GetNRecords", each fnToUse(_, "Sales", itemCount) } ) in Grouped

[@bcti-bcti]

@@alvinzang3738 I whole-heartedly agree. Your solution is MUCH simpler than mine. Thanks for sharing it!!! 👍👍👍