Error export to file: Specified cast is not valid

Sep 14, 2016 at 2:09 PM
Edited Sep 14, 2016 at 2:10 PM
I have a large flat table (over 10MM rows) in my PowerPivot mode and I need to export it.

I connected to DAX Studio from the Excel workbook with the PowerPivot mode.
Then exported the output to file (.txt/.csv) from the following command:
EVALUATE Table

But I got an error: Specified cast is not valid, and the exported file has only the header row in it.

I have tested a smaller file to export to Excel and it worked. But had the same issue when trying to export to file.

Is there something I've done wrong?
Coordinator
Sep 14, 2016 at 8:35 PM
I haven't had this problem before. It's possibly some value in one of your columns. Can you try using SELECTCOLUMNS if you have Excel 2016 or SUMMARIZE for 2013 or earlier to select smaller subsets of columns to see if you can isolate if there are specific columns causing this issue.
Sep 30, 2016 at 5:12 PM
Edited Sep 30, 2016 at 5:12 PM
I get this error often, usually when extracting from large (tall and wide) tables. It's really strange! I end up just using SSIS to save them to .txt
Sep 30, 2016 at 5:58 PM
Hmm.... dgosbell - how does your text exporter handle "NaN" sourced from numeric columns in xVelocity/loaded by PQ?
Coordinator
Sep 30, 2016 at 10:15 PM
I don't believe that xVelocity has a concept of NaN so I'm assuming that these values are loaded in as blanks and would come out as empty strings.. Where are you seeing the NaN values? In the PQ designer or in the PowerPivot window or in Pivot Tables?
Sep 30, 2016 at 10:25 PM
I've seen them in PBI on data loaded from PQ. However it looks like you can create them in PBI directly (at the bottom of this link):
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/