PowerPivot beginner journey 2

I was exploring the data, and found lots of interesting info from my data set. But I could not find how to join some tables in PowerPivot, and after looking at the source data - it turned out my source data used GUID columns, stored as binary, for row IDs - and PowerPivot silently hid it. I would expect it at least show the column and tell me it can't use it. Even better - offer convertion options. But PowerPivot does not even divulge column existense.

The lesson - learn your data before importing it to PowerPivot, afterward it might be too late.

The workaround is of course easy - use SQL query rather than table import, but tedious since PowerPivot makes you re-run the wizard for every single table. Here is the query just in case:

 select *, convert(VARCHAR(40), guid, 1) AS GuidStr from MyTable 

Then I've imported data, added relationships, and started to create calculated columns. One of the DAX expressions got wrong results, and I realized I missed one relationship which would make the expression return correct result. I've tried to add relationship, but PowerPivot did not let me do it, failing with some very obscure error message (An unexpected error occurred, file 'xmvsquery.cpp', line 1739, ...). Only once I've deleted that calculated column, I could create relationship, then add the column back, and finally get the correct results.

Second lesson for me - tell PowerPivot about all the relationships before doing anything with the data.

Overall, I find PowerPivot useful, but such problems make it way less useable as exploratory tool - you've to know all your columns and all your relationships in advance, and tell PowerPivot about everything. If you miss something you might need to go back and start from scratch, e.g. delete a table with all the calculated columns, and re-import it again to get binary column, or delete the calculated columns to be able to define relationships.