I am converting from Hyperion to other tools.  In Hyperion I can take a data source (data table) and create a pivot from it.  The Pivot can contain both Textual data and numerical data.  Some of the pivot columns can be computed and many other options.

In Excel, Pivot Tables, you can replicate this with the same data table.  It yields a results where it tries to numerically aggregate the textual data.

How do you create a textual pivot table in Excel using Pivot Table, PowerPivot or PowerQuery?

Can you show us some of the textual data.  I'm wondering how you would aggregate text as opposed to numbers unless you want to concatenate the values, or that all the values are textual representations of numbers.

Thanks

Gordon

Hi CSWILKI,

According to your description, my understanding is some numbers were stored as text when you use PivotTable, and you cant analysis data with text and number types in a PivotTable at same time.

I suggest you can convert text to numbers first, and then analysis your data in PivotTable. Make sure you change the formatting of the column to general or a valid number format. If you have a large table consists of numbers, you can try the following methods.

Method 1

One option for converting multiple cells into numbers is to use the information drop-down that Excel has provided:

1. Select the range consisting of all the cells you need to convert (making sure that the first cell in the range needs to be converted). The range can include text and other numerical values, as long as it doesnt include cells you do not want to be converted to numbers.
2. Click the warning symbol in the first cell.
3. From the drop-down, select Convert to Number, and all cells in the selected range will be modified, turning the numbers to true numbers.

Method 2

If you have the Background Error Checking disabled and dont see the green warning triangle, try this method for converting cells to numbers:

1. Enter a 1 in a blank cell and copy it.
2. Select the cells containing the numbers, right-click and select Paste Special, Paste Special.
3. From the dialog box that opens, select Multiply, and click OK.
4. The act of multiplying the values by 1 forces the contents of the cells to become their numerical values.

Method 3

In step 3 of the Text to Columns wizard, you select the data type of a column. You can use this functionality to also correct numbers being stored as text. To convert a column of numbers stored as text to just numbers, follow these steps:

1. Highlight the column of text to be converted.
2. Go to Data, Text to Columns.
3. Click Finish. The numbers are no longer considered numbers stored as text.

If my understanding is incorrect, could you upload a sample via OneDrive, and be at bit more precise explain your problem so that we can get more accurate solutions to this problem. I am glad to help and forward to your reply.

Regards,