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!
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 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) } )
This video has been incredibly helpful! I was smiling all the way through as I watched. Thank you!
@@janesheng7113 Im so glad you found it so enjoyable. Thanks for watching.
This is excellent - very useful and very well explained! Thanks
@@petercompton538 thanks for watching!!!!!
A very good tutorial. Thank you for this great job!!!
@@IvanCortinas_ES thanks for watching!!!
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!
Yeah, I encountered that same issue. I found it quite irritating. Thanks for your observations and input.
Great work, awesome! Thank you very much!
Thank you for saying so. And thanks for taking the time to watch.
great as always ❤👍🏻
@@kebincui thank you!!!!
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,
I'm unclear about what you are requesting for the next video. Thanks.
@@bcti-bcti I see that this seems only possible with PQ just ignore my request.
Like before watch, 'cause I know this channel as a top-class soure of information!
This was published accidentally. It will be back up CORRECTLY in a few hours. Sorry for the confusion.
@@willbedeadsoon all fixed. Thanks for being patient.
Great video. Thanks.
but I'm missing the link to the example files.
This was published accidentally. It will be back up CORRECTLY in a few hours. Sorry for the confusion.
@@flyingHubby all fixed. Thanks for being patient.
You forgot the sample file!
This was published accidentally. It will be back up CORRECTLY in a few hours. Sorry for the confusion.
@@jerrydellasala7643 all fixed. Thanks for being patient.
Using Table.MaxN would be much easier to get the same result.
Thanks for the suggestion. Can you provide a sample of M code to demonstrate your technique? Thanks.
@@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
@@alvinzang3738 I whole-heartedly agree. Your solution is MUCH simpler than mine. Thanks for sharing it!!! 👍👍👍