Strange behavior on Text fields importing to PowerPivot Windows

All replies

  • why do you think those rows get truncated?

    creating a calculated column as "=LEN([Remarks])" reveals that nothing got truncated
    also the Pivot Table shows whole text

    Gerhard Brueckl
    blogging @
    working @

    Wednesday, April 30, 2014 9:19 AM
  • No,  you try import the backup worksheet (52 row) to fresh powerpivot windows, there are five row remarks got truncated

    for sheet1, which is from 19 to 52,  those five row remarks fields are not truncated, but the moment I insert one row above row 19,  they got truncated

    For the backup worksheet,  if I sort the validation fields and import again,  those 5 rows are normal

    Wednesday, April 30, 2014 9:39 AM
  • Ok, seems that Power Pivot only checks the first 8 rows and determines the datatype of a column based on this sample

    as 8 seems quite odd as a sampling rate i assume that there i limit in terms of size e.g. 100kB which in your case results in 8 rows
    I will try to clarify on this

    in your case, if a text longer than 255 appears within the first 8 rows it works just fine (thats just a finding, not a solution :) )

    another strange issue is that Power Pivot only knows the data type text and this data type is supposed to be independent of the length of the actual string
    your case more or less proves that this is not the case and different datatypes are used internally
    I will also try to clarify on that one

    Gerhard Brueckl
    blogging @
    working @

    Wednesday, April 30, 2014 4:51 PM
  • Hi Gerhard,

    Appreciate very much on your analysis and findings,  this issue had been bordering me for weeks as my user require the source table to general pivot table and charts for management.  Fortunately there are still workarounds.   

    Awaiting to hear from you soon.  :)

    Thanks !!

    Thursday, May 01, 2014 1:01 AM
  • Bluesky,

    Is this still an issue?


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, June 28, 2014 12:21 AM