In the Select Data Source box, choose Switch Row/Column, as the thumbnail shows below. Right-click in the white area of the chart and choose Select Data. If my intention was to change the headings on the X-axis, that must be accomplished differently. The chart below was produced from the original data and then the transposed data.
Perhaps you are thinking that I might have transposed the data in order to change the look of a chart produced from the data.
This procedure is all about changing the look of the data. The data, as seen below, has been rearranged with a minimum of effort. Step Three (continued) - Click into the box beside Transpose and then click on the OK button. Step Three - Click on the down arrow under the Paste button on the Home tab, and select Paste Special In my example I clicked into cell A8 to select that cell as the place where I wanted the data to begin. Step Two - Select the cell where you want to move the data. Step One - Copy the entire range of data, including the headers in row 1 and column 1. However, after looking at the data, I wished that I had organized it differently as below. Excel has a much better option - transpose the data.īelow is my worksheet as originally set up. The old, slow way to accomplish that might involve clicking and dragging data or even starting over. Basic, Group by:Action User, New column Name:Count, Operation:Count Rows Īnd modify resulting code from = Table.Transposing Rows and Columns in MS Excel - 2007Īfter entering data in a worksheet, I decided to switch the location of the headers contained in row 1 and column 1. The overall trick is to get a new column with cumulative count on Action User into the table: | Employee A | Employee B | Employee B | Employee B | Employee C | Employee C |Ī pivot ends up like this: | Employee A | Employee B | Employee C | Employee D | I could do it with VBA or a series of Transpose formula outside of Powerquery but feel this isn't the best way to go - formula would need updating each week to take into account the different number of rows.Ī basic transpose doesn't work as it ends up like this: | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | I have a separate table ( TeamMembers) that lists unique employee names that I've tried joining to create this list but ultimately end up with versions similar to the original table. I'm looking to convert it to look like this (it'll have column headers, but I'm not bothered what they are): | Employee A | 1 | | | | |
I have a PowerQuery table called "On Holds" in Excel as below: | Action User | Index |
0 kommentar(er)
