Power Query Help in Power BI

0
votes

I am working in Power Query with normalized tables in Power BI, and need to manipulate a table in order to create a many-to-one relationship to another table. Consider the following example. I have a table with the columns "Employee ID", "Metric", and "Value". Each employee ID has the "Metrics" Name, Gender, Age, Department, and Experience. Currently, the table is structured so that each Employee ID is repeated 5 times, one for each metric, and has a value for each in the "Value" column.

I need to pivot the data so that each unique Employee ID has a row, with separate columns for each metric, and the Values in the corresponding cells. Each employee ID will have a row, and the columns will be "Name", "Gender", "Age", "Department", and "Experience". Is there an easy way to do this in Power Query in Power BI?

I tried a pivot, and this creates the columns as desired, but still repeats the Employee IDs. For example, each employee will be listed in a row, and the "Name" column will be filled. All of the employee IDs will be listed a second time in the "Employee ID" column, but now the "Name" column will be blank, and only "Gender" will be populated. And this continues for all metrics

Any help is much appreciated. Let me know if you have any questions.

1
powerbipowerquery
Does the table have a unique ID column? It sounds to me like it's trying to pivot but there are other column(s) that are preventing it from combining the rows like you want. – Alexis Olson
Hi Alex, the ID column is unique in that each Employee ID will only have one value for each metric. The Employee IDs are duplicated for each row though. I have been using the solution below but it does not seem work correctly, as I am still getting the same result with the Metrics as columns, but the Employee ID repeated in a new row for each different Metric. – lizard12012
A more detailed description can be seen in the comment below. – lizard12012
The table does have another unique ID column! It needed to be deleted before the pivot worked. Thank you. – lizard12012

1 Answers

1
votes

Go to Transform, select the Column Metric and press Pivot.. (Draaikolom in Dutch) enter image description here

You get a popup, select Value as your Value Column.

enter image description here

Result is not yet what you want: enter image description here

Go to Advanced table Editor (under start tab). Remove the List.Count (including comma)

enter image description here Press ok, end result is what you are looking for:

enter image description here