Let's assume that we have a table with the below heather:
EmployeeID | Location | Skill1Level | Skill2Level | Skill3Level | Skill4Level
The Location field contains the name of the city where he is based and each skill level is a value between 1 to 5.
What we want to achieve
We need to show in a chart what are the skills level in a specific location. Additional to this, we need to have a line chart that shows all the skills cross locations and the ability to specify what skills and location to show on the charts (Slicer)
Problem
Except for the Splicer, we could drag and drop all the columns inside the Values of each chart to get what we want. The input table has data already pivoted and makes it almost impossible for us to generate and make the Slicer to work.
Solution
I found multiple solutions, but the one that was recommended by one of my co-workers was to do an unpivot to the table. The solution is simple, clean and you end up with your input data in a format easy to manage and manipulate. In the Applied Steps of your table you shall add the following step:
= Table.Unpivot(#"NameOfPreviousStep", {"Skill1Level", "Skill2Level", "Skill3Level", "Skill4Level"}, "SkillName", "SkillValue")
The output is a new structure of the table that looks like that:
EmployeeID | Location | SkillName | SkillValue
For each employee, you have multiple rows (one row per skill). From now generating charts and slicers where you use the employee location is easy
Comments
Post a Comment