Pivot table

[Anonymous]

I have a table which contains the columns: employee, salary, hourly, bonus, compensation. I want to create  chart which has has three columns: employee, pay type and pay. The employee column would be contain the same employee name as in the first table, the pay type would be either salary, hourly, bonus or compensation. There would be multiple rows per employee, one for each pay type, but only if there is a value in the corresponding column in the first table, if it is blank there would be no row for it. And then the pay column would have the dollar amount from the first column.

Can this be done in Power BI? If so, how?

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

If I understand correctly, the table contains five columns: Employee, Salary, Hourly, Bonus, Compensation. And you want to get the table with three columns: Employee, Pay type (contains Salary, Hourly, Bonus and Compensation) and pay, right?

In your scenario, you can open Query Editor, then check the Employee column, click Unpivot Other Columns. If original table columns Salary, Hourly, Bonus or Compensation is blank, there is no row for it after this operation automatically. See:

1. Assume the original table is below:

2. Use "Unpivot Other Columns" option.

If I misunderstand your requirement, please share some sample data and screenshots about desired results for our analysis.

Best Regards,
Qiuyun Yu

[Anonymous]

Perfect. Thanks!

[Greg_Deckler]

Power BI does not have a true pivot table visualization with all of the features/functionality of Excel pivot tables. However, for you needs, it sounds like the matrix visualization should be able to do what you want.