PivotTable Text Values Alternative
16.9 هزار بار بازدید -
3 سال پیش
-
Excel users who want to
Excel users who want to learn how to streamline their work click here 👉 https://affiliate.excel-university.co...
Subscribe to blog for free Excel tips to your Inbox:
https://www.excel-university.com/subs...
Structured on-demand Excel training:
https://www.excel-university.com/trai...
One limitation of a traditional PivotTable is that we aren’t able to place text fields into the values layout area. Well, technically we can, but we get a count rather than seeing the text value. In this post, I’ll demonstrate an alternative way to display text values that uses Power Query (instead of a PivotTable). Depending on the type of report you are building, this may be a nice option.
We will create the desired report in three steps:
Get data into Power Query
First we need to get the data into Power Query. We can do this by selecting the data table and clicking Data .. From Sheet (or depending on your version of Excel, Data .. From Table/Range.) The data is pulled into the Power Query editor
Pivot Transformation
Next, we select the column that contains the values we want to use as our column headers. This is the same field we would put into the Columns layout area of a PivotTable. In our case, we want one column for each value in the Return column, so we select the Return column.
We then click Transform .. Pivot Column. In the resulting Pivot Column dialog, we select Staff as the Values Column because it contains the values we want displayed.
Now, the next setting is important and is different than the way a PivotTable works.
Next, we click Advanced options .. Maximum (or Minimum) in order to display the text value instead of the default Count
Load to Excel
To get the data back into Excel, we click Home .. Close & Load To … and we send it to a Table in an existing or new sheet.
The key to this technique is that we use the Min or Max aggregate function in the Pivot Column transformation. It is important to note because it is different from the way PivotTables work.
Oh … one more thing … using Min/Max in Power Query also works with the Group By transformation.
If you have any thoughts or suggestions, please share by posting a comment below … thanks!
Subscribe to blog for free Excel tips to your Inbox:
https://www.excel-university.com/subs...
Structured on-demand Excel training:
https://www.excel-university.com/trai...
One limitation of a traditional PivotTable is that we aren’t able to place text fields into the values layout area. Well, technically we can, but we get a count rather than seeing the text value. In this post, I’ll demonstrate an alternative way to display text values that uses Power Query (instead of a PivotTable). Depending on the type of report you are building, this may be a nice option.
We will create the desired report in three steps:
Get data into Power Query
First we need to get the data into Power Query. We can do this by selecting the data table and clicking Data .. From Sheet (or depending on your version of Excel, Data .. From Table/Range.) The data is pulled into the Power Query editor
Pivot Transformation
Next, we select the column that contains the values we want to use as our column headers. This is the same field we would put into the Columns layout area of a PivotTable. In our case, we want one column for each value in the Return column, so we select the Return column.
We then click Transform .. Pivot Column. In the resulting Pivot Column dialog, we select Staff as the Values Column because it contains the values we want displayed.
Now, the next setting is important and is different than the way a PivotTable works.
Next, we click Advanced options .. Maximum (or Minimum) in order to display the text value instead of the default Count
Load to Excel
To get the data back into Excel, we click Home .. Close & Load To … and we send it to a Table in an existing or new sheet.
The key to this technique is that we use the Min or Max aggregate function in the Pivot Column transformation. It is important to note because it is different from the way PivotTables work.
Oh … one more thing … using Min/Max in Power Query also works with the Group By transformation.
If you have any thoughts or suggestions, please share by posting a comment below … thanks!
3 سال پیش
در تاریخ 1400/07/09 منتشر شده
است.
16,992
بـار بازدید شده