If you’ve have messed with pivot tables in the past, you know that adding data to an excel spreadsheet is nothing new. You now can add data three ways, because you can now add data with Power Pivot and Power Query. The question is which tool should you use when? Well if you have Office 2013 and didn’t sign up for the Power BI preview, you are limited to using Pivot Tables. Since I signed up for the Power BI preview I was left with another question. Which tool is the right one to use for loading data? Well it’s the same answer you always get when working with computers. It depends.
Power Query was designed to readily provide access to data, any data. Microsoft on their site promotes the fact that you can include, or as they like to say “Mash” data from Wikipedia or other sources together with your business data. I can see where this feature would be very useful. I work with a concrete company who has a lot of KPIs that they have for daily production. Well, when it rains they don’t meet the performance metrics because no one wants to pour concrete in the rain as it affects the way it cures and the concrete can end up bumpy instead of smooth. If they correlated the weather together with the KPIs they could definitively determine the effect of rain on their business. Power Query also provides a way of merging or appending data together, which makes sense since you may be selecting data from a variety of sources
If you are pulling together internal data so that you can create a Power View report on it, Power Pivot would be the best way to proceed. Yes, you can do this in Power Query, but Power Query doesn’t provide the same level of filtering that Power Pivot does. Power Pivot also provides the dangerous feature of allowing users to update data in the database by modifying the data in Power BI a spreadsheets, a feature which most of the time I believe will be turned off.
With Power Pivot, not only do you load up the data, but you also build the relationships between the data. It works the same way if you user Power Query for half the tables in your database and Power Pivot for the other half. I felt compelled to check that out when I was playing around to see if it would make a difference. Of course Power Pivot can do a lot more than just relationship management of data.
Data aficionado et SQL Raconteur