I just went through the hassle of forcing IT to upgrade me to MS Office 2010 so that I could utilize Power Pivot. After spending 2+ hours trying to get it to do everything I can make it do in plain ole Excel, I gave up and went back to Excel.
I know that my problem is that I speak Excel and not Dax and not that it isn’t capable. But why couldn’t they just leave the plain old lookup and match alone? The main reason I am switching to Power Pivot is to deal with the number of lines, not advanced analytics, so I may be asking it do something too simple…
I’ve used PowerPivot to some extent (not as much as I should have, really, in my job) so I can try to help - what exactly are you trying to do? The tutorials on Microsoft’s website are pretty good, I’ve found. PP is really not much like regular Excel at all - it owes more to Microsoft’s BI heritage than to the Office family, but it can be extremely powerful, particularly if you have large volumes of data.
Could you give us an idea of what you’re trying to accomplish?
I am trying to do what in Excel is a simple Lookup from another worksheet. Based off of what I’ve found in my research, I need to make it a related table. I can import the other data and get it to create a relationship between the columns, but not a related table because the related table option is greyed out.
I want a fairly straight forward equivalent to a Match or Lookup function. If Column B on worksheet X says 123456, find 123456 on worksheet Y Column C and give me the information from Worksheet Y Column D and put it on Worksheet X Column G
Hmm, upon re-reading a tutorial for the 50th time, maybe I need to create a third table instead of importing just the one. Agggh. Why can’t this be more intuitive.
oooh. I think I got it to work!
Dervorin - Don’t think you are getting off easy! I am just getting started and this was a 101 (hell even lower than that) question.
Damn. Are you telling me I need to actually brush up my PowerPivot skills?
I agree that relationships and lookups in DAX are neither as simple nor as intuitive as they were in Excel formulas, or even in SQL. However, DAX is relatively new and being constantly improved, so I’d expect this to improve with time.
One suggestion I have is to try and make your source data flatter - is there any way you can join the tables together before you import into PP? That’s often easier than trying to create relationships between objects in PP. Also, PP is designed very much around a “star schema”, where “facts” in a central table are surrounded by “dimensions” (to use the language of dimensional modelling) rather than an ad-hoc lookup structure, though that can also be done.
The main reason that I am moving to power pivot is that my primary source data is a .csv with over 500k lines that chokes my plain Excel and I don’t have access to the original database to modify the output. My table that provides the lookup key, is constantly changing through manual entry when something new shows up on a pdf.
I thrashed around with it for about a month. I upgraded to Excel 2013 in order to work with PowerPivot.
I work with data tables that have millions of records.
I use an ADO connection to pass variables into a SQL stored procedure and then bring anywhere from 3000 to 100000 records back into my excel spreadsheet.
The big problem (like yours) is not the capacity of Excel but the speed in which it solves for lookups, indexing, sumifs etc when I work with that final table. That’s where I was hoping PowerPivot would come to the rescue.
Powerpivot shows promise by the fact that I can use that table in the datamodel and synthesize my info (very very) quicker.
BUT IT DOESN’T WORK!
For some reason any 2013 Excel file that has the PowerPivot addin enabled will crash if there are any ADO macros in it. I haven’t yet seen anyone else who has reported this problem. It doesn’t crash when running PowerPivot nor does it crash when running my ADO macro. It just plain crashes after loading.
Disable PP and the spreadsheet doesn’t crash.
I’m not giving up on PowerPivot. The calc speed is a real enticing drug to me. So I’ve tabled my work with it for a few months to see if maybe there are upgrades in the future that fix my problem.
