June 25, 2016
I have a general question, and not sure if this is in the right forum, so move if it isn't.
I've completed the PQ series and it was great. I have some PowerPivot experience but am now going through the PP course to see what basics I've missed and then move on to the advanced stuff.
To me it seems the PP data pulling tools are fairly basic compared to what PQ can do. So my question is this: Unless you are just pulling together some basic data, which PP can easily handle, would it make more sense to just use PQ to pull together the various tables, loading each one as only a connection and "Adding to Data Model" and then letting PP take over after that?
In the past, I used Access to do this where I couldn't get PP to do what I wanted first, but it seems PQ will do this and it is more self contained than working in Access first.
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Excel Customer Service
November 8, 2013
Yes, you got it right, Power Query is the best for importing and transforming data, loading the queries to data model for Power Pivot is the next natural step.
Also, in many cases, it is recommended to add calculated columns in Power Query than in Power Pivot.
June 25, 2016
August 16, 2018
That was a good question Ethan. I have just started the Power Pivot course and came to the forum to see if others thought the same. I will continue on with the course but could I manage skipping the Power Pivot section and just link as a connection from Power Query. Will I be missing some valuable information that I could not live without?
July 16, 2010
Best practice is to use Power Query to get your data, clean it and add any calculated columns you need, then load it to Power Pivot. This reduces the calculation load on Power Pivot and will make your model more efficient, on top of the improved data cleaning options you have in Power Query.