Dynamic Group by (referencing cell value)

[AvG]

Hi there! My goal is to set up a dashboard in Excel with a user-friendly UI and easy drop-downs to dynamically filter tables/charts by different segments.

On the back-end, the source data should be filtered accordingly by referencing those dashboard toggles (and occur quickly with little lag time).

I tried Power Query for the very 1st time. How do I dynamically change "Group Rows" by referencing a cell value? I've looked at the Advanced Editor but a good tutorial seems lacking.

For example, if the dashboard dropdown set to "All grades" and "Sciences", then group rows only by "Subject". If dashboard is set to "Grade 1" and "Sciences", then group rows by "Grade" *and* "Subject".

Thanks!

 

[Ken Puls]

Well, to be fair, you just asked for something super complicated here... Trying to read from an Excel table is not hard (simply set up the table and use "from table" to pull it in.) The issues you're going to be hit with here is that you're asking for complicated conditional logic (in that it drives grouping levels) plus you're going to have to avoid the formula firewall. In other words, you probably hit a complication level of 8 out of 10 for your first day.

The other thing you're asking for is "little lag time". Depending on how much data you have, and how you structure this, that may be a show stopper for you. Power Query is not quick generally. If you're expecting this to be as fast as regular excel... well.. it just won't happen.

I just want to set your expectations here before you decide to pursue this tool for the purpose.

 

[AvG]

I just want to set your expectations here before you decide to pursue this tool for the purpose.

Thanks for clarifying that, and for the quickly reply too!

So do you recommend Pivot Tables in this case?

 

[Ken Puls]

If you want quick (re) calc, then yes.