Excel Forum

Use PowerQuery for PowerPivots?

ehans

Member

Members

Power BI

Forum Posts: 14

Member Since:
June 25, 2016

Offline

December 17, 2016 - 3:27 am

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.

See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1824

Member Since:
November 8, 2013

Online

December 17, 2016 - 5:23 am

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.

Answers Post

ehans

Member

Members

Power BI

Forum Posts: 14

Member Since:
June 25, 2016

Offline

December 19, 2016 - 8:03 am

Thanks Catalin. That is what I was thinking, but just wanted some confirmation before I headed down that road and then ran into a big "gotcha" somewhere.

Samantha McNally

Member

Members

Forum Posts: 8

Member Since:
August 16, 2018

Offline

September 5, 2018 - 10:32 pm

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? Confused

Mynda Treacy

Admin

Forum Posts: 4506

Member Since:
July 16, 2010

Offline

September 7, 2018 - 9:07 am

Hi Samantha,

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.

Mynda