PDA

View Full Version : Import Excel - force all columns as text



Martinl
2017-01-16, 08:44 AM
I receive each month a flattened pivot table which I need to normalise.
Every month this table grows by 1 column currently standing at 108 columns, so I use the un-pivot other columns function.
My problem is that the headings are all in dd/mm/yyyy format and the column entries are all in whole numbers.
Currently I have to manually adjust the Change Type function to include the additional column each month
Keep only the last 4 columns - months
Then Promote the Headers as it appears that only text will get promoted to a header
Un-pivot the table
Change the type of the Values to Whole number
I would like power pivot to import all the columns as Text so I can then do away with the manual intervention each month
The rest should then be simple.
Has anyone any ideas on this, I did some reading on this site and thought that maybe Manage Parameters would be the way to go, but I got totally lost.

El Cid
2017-01-18, 11:19 PM
http://www.excelguru.ca/forums/showthread.php?6843-Apply-a-function-to-all-columns-in-a-query