Forum Discussion
Trying to import a seemingly simple excel sheet.
While this seems straight-forward, I'm getting tripped up. Below is data from Excel that shows a management team and their projected Headcount needs for the rest of the year. (Between the Youtube videos and searched threads, it's obvious I need to stop thinking they way I do in Excel.)
What I'd like to do is show the sum total headcount (last row) forecast over time in a line chart. What I've done so far is deleted the top row, and then transposed it. That gets me to columns that I can work with but when deleting the top row, I've lost the time axis. (I could change it in Excel but I don't own the file). How can I either transform the data below to maintain the remaining months in 2024 or, is it possible to do what I've done but insert a column with time?
| Manager | Site | Mar 2024 | Apr 2024 | May 2024 | Jun 2024 | Jul 2024 | Aug 2024 | Sep 2024 | Oct 2024 | Nov 2024 | Dec 2024 |
| Gilbert | Arizona | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
| Gilbert | North Carolina | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 | 11 |
| Gilbert | Colorado | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| Thomas | Washington | 2 | 2 | 2 | 3 | 3 | 3 | 2 | 2 | 2 | 2 |
| Thomas | Oregon | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 7 | 7 | 7 |
| Delores | Kentucky | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Delores | Ohio | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| Delores | Iowa | 2 | 2 | 2 | 2 | 1 | 1 | 1 | 1 | 1 | 1 |
| Beaufort | Florida | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| Beaufort | Georgia | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Beaufort | Alabama | 10 | 10 | 9 | 9 | 9 | 10 | 10 | 10 | 10 | 10 |
| Beaufort | Tennessee | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| Total | 47 | 47 | 46 | 47 | 46 | 47 | 46 | 48 | 48 | 48 |
You have to select all columns other than the Month column and then use "Unpivot Other Columns". Before that ensure that there are no total/subtotal rows. Also, in the Source step itself, the headers of the table should be the ones that you posted in the first message.
5 Replies
- Ashish_Mathur
Super User
Hi,
In the Query Editor, select the first 2 columns, right click and select "Unpivot Other Columns". Rename the attribute column as Date. Ensure that the data types are proper. Click on Close and Apply. Now build your visuals/write your measures.
Thank you! I think you've got me just about there. I can rename the 'Attribute' column to 'Date' but the values in there are just text (see below) and not actually the dates. I should mention, the below is if I delete the top row from my original post.
If I don't delete the top row from the original post, I end up with the below.
- Ashish_Mathur
You have to select all columns other than the Month column and then use "Unpivot Other Columns". Before that ensure that there are no total/subtotal rows. Also, in the Source step itself, the headers of the table should be the ones that you posted in the first message.
Thank you, Thank you!
- Ashish_Mathur
You are welcome.
