Forum Discussion
Multiple Columns Displaying on a Visual
Hi,
I am a reltaive newbie to PowerBi, so I'm sure this is probably a (very) straightforward issue. I have 3 tables (below). I have connected Project X and Project Y with the Master Resource table via the 'Resource Name' field. This provides me with a visual which shows Resource Name, Department and FTE values.
However, I get multiple columns for JAN and FEB FTE (i.e. one for Project X and one for Project Y). Ideally, I would want a single column for January and a single colum for Feb (please see Desired and Current Output below). Both Project X and Project Y have the same columns, so I supposed could append one table to the other, but there are multiple projects, and more to be added as the programme becomes bigger; as such I was hoping for a simpler and more elegant solution.
MasterResource Table
| Resource Name | Department |
| Adam Smith | HR |
| Karren Jones | IT |
| Jenifer Cooper | Accounting |
ProjectX Table
| Resource Name | JAN FTE | FEB FTE |
| Adam Smith | 0.5 | 0.7 |
| Karren Jones | 0.2 | 0.3 |
ProjectY Table
| Resource Name | JAN FTE | FEB FTE |
| Jenifer Cooper | 0.5 | 0.7 |
Current Visual Output
| Resource Name | Department | JAN FTE | FEB FTE | JAN FTE | FEB FTE |
| Jenifer Cooper | Accounting | 0.5 | 0.7 | ||
| Adam Smith | HR | 0.5 | 0.7 | ||
| Karren Jones | IT | 0.2 | 0.3 |
Desired Visual Output
| Resource Name | Department | JAN FTE | FEB FTE |
| Jenifer Cooper | Accounting | 0.5 | 0.7 |
| Adam Smith | HR | 0.5 | 0.7 |
| Karren Jones | IT | 0.2 | 0.3 |
2 Replies
Anonymous , Merge two tables in power query or Create a combined measure
JAN FTE M = sum(ProjectX[Jan FTE]) + sum(ProjectY[Jan FTE])
FEB FTE M = sum(ProjectX[FEB FTE]) + sum(ProjectY[FEB FTE])
Change sum to Average/Min/Max as per need
or
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
