Thursday, November 14, 2013

Power Pivot: unable to convert a value to the data type requested

I just ran into an (for me strange) issue when I was updating a Power Pivot workbook.
I had 2 columns in a table, let's say "One" and "Two", where One has integer values and Two string values. In my model I created a calculated column Three to concatenate these values. Because One and Two are actually redundant and never used on its own, I decided to delete those columns and save some space (the tables contained just over 2.2 million records).
So I started with updating my source view to concatenate the values in T-SQL, but when I refreshed the table in Power Pivot I got the error:
"Unable to convert a value to the data type requested in table x for column Three"

This error occured because I renamed the column in the view One and I already had that column present in my PP earlier (although I deleted it before opening the Table Properties). So my column One was previous INT, but would now be VARCHAR, however PP keeps this setting in mind although the column was deleted.

I found an answer on StackOverflow:
http://stackoverflow.com/questions/6981688/powerpivot-sql-query-fails-unable-to-convert-a-value-to-the-data-type-requeste

My solution was to recreate the column One in my PP and leave it empty (this would get the data type INT), and then add the new column One from the source in the Table Properties. This gives a warning that the column is already present in your workbook, but solves it by renaming the new column to "One 1" when refreshing the changes. After that you can delete the old column One and rename "One 1" to One and you're done!

So in short:

  1. Recreate the column (One) you deleted
  2. Then add the newly added column from your source with the same name
  3. PP renames the new column to "One 1"
  4. Delete the old column (One) again and rename "One 1" to One, or the desired name

Tuesday, November 5, 2013

SQL Rally Amsterdam 2013


Of course I would have loved to go to the PASS Summit this year, but unfortunately that wasn't going to happen according to my employer..
But then I heard that SQL Rally was coming to Amsterdam and saw my opportunity.
I read several posts from AllenBrent and PASS itself on the topic How to Convince Your Boss... I had my hotel reservation ready, my arguments lined up (with even some backups to negotiate).. But he just said: "Well, that sounds like a good idea, just book it! :-)
The only catch was that I was asked to talk about a few of the topics on a study night to some of my co-workers. Well, that wouldn't be too hard I guess!

I hope to see a lot of my SQLFamily (Twitter-)friends in Amsterdam in the next 3 days! Do grab on to me and we'll have a chat!

Featured Post

Governance & Administration - Tenant Settings: Visual Cues

With all the Fabric announcements in the last months, some of the Admin announcements might have slipped through. As you might know, the Adm...