Hello,
I work in HR and would like to set up an interactive Dashboard for Headcount and Age Analytics using Power Query and Power Pivot.
I have liinked a folder with monthly headcount reports to Power Query where besided soem formatting I calculated the age based on the fiield 'birthday' and length of service based on the field 'hire date'. So far so good...
In the end I would like to apply slicers and charts to a dashboard with different age groups (e.g. <25; 26-35; 36-45; etc) and also customised length or service groups.
My first question is where should I do the grouping - is it in power query or in power pivot?
Second question is how to do that most effectively.
Many thanks for your help!
David
Hi David,
You should do that in Power Query to define age groups. See this article for a similar problem.
Hi Catalin,
Thanks for the quick response.
In the meantime I had also looked for options and found a different approach:
- I created a mapping table (Age/Age Groups) and added this to the data model
Age Age Group
20 <25
21 <25
22 <25
...
56 <56
The I created the relationship between age in the data table and age in the mapping table.
It seems to work as well, at least I could create charts with slicers. I only need to find a way to change the sorting order of the slicer but found there are options to do that.
Your solution sounds much more complex. Maybe I am missing something but is this solution also fime from your perspective or are there issues I have overlooked?
Thanks,
David
Yes, a lookup related table will work too, I thought it's a power query only solution, sorry.
