Power Pivot versus Power Query: Power BI Dilemma

March 16, 2016 at 1:58 pm

Avi,

Agree 100%.

The more I learn about Power Query, the more I am drawn to it as a solution as well as a supporting utility.

March 16, 2016 at 2:03 pm

Hi Avi,

Does the switch to Power Query cover Linked Tables as well? i.e. Would you write Power Query routines to load small reference tables from the current Workbook or would you simply click on the “Add to Data Model” button in the POWERPIVOT Tab? (Excel 2013)

Thanks,

Ted

April 2, 2016 at 4:47 pm

Ted, Now that I think about it. I do simply click “Add to Data Model” for these ones. But only if they are under my strict control. If there is a mapping/reference table that may be 2-3 other users need to touch, I put them all in a separate file (place it on a SharePoint Library or Network Share) and use PQ to suck it in.

March 16, 2016 at 2:50 pm

Thanks for the post. I also use Power Query widely to grab, reshape, and prepare data for use by Power Pivot (either by “loading to…” the data model, or by adding from existing connections within Power Pivot — the latter option for models built before upgrading to Excel 2013).

But a Problem I run into frequently is: if I ever change even a tiny part of the Power Query (say, changing a filter, or pointing to a different source file), either the Power Pivot table and associated pivot tables disappear entirely, or Power Query gives me a yellow triangle and tells me it cannot refresh the table. I then have to recreate them (rebuilding relationships, recreating slicer panels, etc.). This is happening under Excel 2013; at least with Excel 2010 I could get around this by copying the new query string into the PPVT connection; this no longer seems to be an option.

Lately I’ve been taking pains to avoid this risk by a couple of means:
– Using Power Query to generate csv output, and then feeding Power Pivot with the csv — obviously sub-optimal, and requiring some VBA code if generating millions of rows. But csv input, however primitive and space-wasting, is very robust.
– Parameterizing my Power Queries where possible, so I can change input sources in an Excel table without even opening the query
– Keeping PQ filtering to a bare minimum. If I later want to filter the table down, I can usually find a way to do it in the Power Pivot window (Table Properties)

I wonder if these issues are common to other users, or if I am missing some elementary step.

March 17, 2016 at 5:57 am

Hi Bill,

I took the same approach on some of my projects. It has its benefits, but VBA has trouble generating 2Go + .csv files.

What I tend to now is create a table with a 10 headers and 1 row in excel, name it “my_measures”, send it to PPvt and build all my measures in there. It’s both clearer, more flexible, and safer for future PQ edits.

March 18, 2016 at 3:32 am

I also keep my measures in a ‘measure table’. This is just a table with no data on rows, where I save my measures in case Power Query decides to delete my data table. You keep all of your measures this way no matter what happens to your data table.

March 22, 2016 at 5:12 pm

Bill, I have experienced the same problem. Whenever I make seemingly small change or greater in Power Query, the tables in the data model disappear in PowerPivot! This to me is somewhat of a deal killer for using the tools in combination. To solve I use both separately. Process the data set(s) in PowerQuery, load to Excel, dump table out to delimited file. Pull into PowerPivot in some fashion. This is not what I expected to have to do. And yes, I keep PQ filtering to a bare minimum. I also now make sure I have dates formatted as dates and not (int or text) YYYYMMDD format. That way I can use the inbuilt date functionality of PowerPivot.

March 16, 2016 at 5:22 pm

From what I read 2016 should solve this problem

March 17, 2016 at 5:36 am

Publish to SSAS? You mean publish to SharePoint? Power Query is indeed not supported on SharePoint, so data must be extracted in Power Pivot itself.
Not exactly an edge case scenario, some customers want to use Power Pivot (+ Power View) on SharePoint.

March 18, 2016 at 3:30 am

Have you checked out Power Update? I have a lot of SharePoint deployments and thanks to PowerUpdate, I have been able to use Power Query instead of the native Power Pivot import tool alone. Not only this, but you can update the workbook on the hour, not just once per day.

March 18, 2016 at 5:04 am

This is interesting, Koen. Are you saying that if my data resides principally in SharePoint, it may be better to bypass PQ and use PP straight off?
I am certainly having issues with my development: PQ can read all the files in any given folder – and that functionality is really useful to me – but it can’t do the SharePoint equivalent…

March 17, 2016 at 8:38 am

Power Query is at it’s best when grapping from very differently shaped sources. It can easily convert data, replace values, filter and unpivot etc. Also when you merge queries with other than one-to-one relationship, then Power Query is your way to go.

I personally use more Power Pivot since it’s much better when connecting SSAS. The Design mode allows you to shape your data to a MDX query what can be loaded quickly. What I feel is not there yet with Power BI is the opportunity to design SSAS query (without live-mode). Connecting SSAS with a Get Data function is not very powerful.

March 17, 2016 at 9:30 am

One thing I noticed that Power Pivot allows and Power BI does not, is being able to select and copy results in a table. There are times I want to simply copy a calculation result, just one number in a cell, and pasted it in an email.

I know in the February Power BI update we can export data in a table now, but that is more mouse clicks then I want.

March 17, 2016 at 3:54 pm

By bringing all data into the Data Model via Power Query you eliminate one limitation of PowerPivot, which is not being able to change the source type of the data (ie Excel, CSV, database table). This is especially useful when you initially start building a solution, and perhaps do not have the ultimate future data source in place.

March 18, 2016 at 3:27 am

Flattening tables in Power Query can be very useful, especially in terms of lookup tables. Preventing multi-table lookups is a great way to leverage Power Query. I understand where you are coming from, though. You certainly would not want to flatten a table when a lookup and data table structure makes sense. And please … never bring date fields into a data table.. use a calendar table!

For me, Power Query eliminates the need to create any calculated columns in Power Pivot at all. Since I really learned Power Query and M, I have not written a single DAX column.

Beyond the above two, I just follow the general rule to use the combination of PQ or PP that is most elegant.

March 18, 2016 at 8:10 am

Avi,
I have a situation in wich i need to do a lot of ETL things with PowerQuery to perform nice data model and send it to PowerPivot after. But as you told before, if I want to use my PowerPivot model later in SSAS, I can’t use PowerQuery to clean my data from different sources. Do you know is there any workaround to use PowerQuery with SSAS or when SSAS will support PQ?

Best regards, Evgeniy.

March 18, 2016 at 9:20 am

I’ve got same situation as @Evgeniy with Power Query, I need it to clean up knarly file, but then won’t be able to migrate the workbook to SSAS later. Haven’t used SSIS for years, not sure if it can replicate PQ transformations.

March 23, 2016 at 4:56 am

Hm, strange that no one has shouted out „Power Update“ here so far. Best compromise for SSAS-deployment that I can think of at the moment:

If you design your workbooks like Andrew (atodd12345) described (separate your data-tables from your “measure”-tables), it not that a big deal:

1) Duplicate your workbook
2) In workbook 1 use schedule Power-SQL-update to “divert” all queries that currently feed the PP-datamodel to SQL-server-tables. This will be your leightweight SSIS-server (https://powerpivotpro.com/2015/09/power-query-as-an-ssis-alternative/).
3) In workbook 2 delete your data-tables and recreate them connected directly from Power Pivot to your new SQL-datasource. Re-establish the table-connections: Ready to be deployed to SSAS.

March 23, 2016 at 5:32 am

And – just in case it’s not obvious yet – you should to follow Andrew’s other good advice as well: Create your calculated columns in Power Query instead – otherwise you’d have to re-create them during re-creation of your SQL-connected tables.

March 23, 2016 at 5:53 am

Sorry for the spam, but hey: It’s not me to blame – it’s these incredibly awesome team at Microsoft – listening to customers needs and delivering at lightning speed:

Looks like you don’t need to invest in SSAS-tabular, if performance of PBI is enough. This will become your SSAS in the cloud: https://blog.crossjoin.co.uk/2016/03/23/thoughts-on-the-power-bi-announcements-at-the-ms-data-insights-summit/?utm_content=bufferdc3e2&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

April 19, 2016 at 2:09 pm

Very nice post. A few comments:

“Please! Don’t use Power Query to flatten data into a single table…”

In general, I always flatten snowflake relationships to make querying data easier (e.g. Product Category–>Product Subcategory–>Product). This recommendation by Ralph Kimball in respect the data warehouse design, has worked well for me. Also, if a related table has only a column or two of data that I will ever need, then I’ll flatten the relationship. In most cases though, I agree that the data shouldn’t be flattened..

“In general, I eschew doing any math/calculations in Power Query”

Agreed, but the statement must be qualified. If you’re doing calculations that are meaningful at the row level, then it would make sense to do the calculations in Power Query. Measures involve aggregated data, so in most cases, data that must be aggregated, sliced & filtered would be created in Power Pivot. There are situations where a fixed structured grouped and aggregated table is all that is needed for a particular report, and in these situations, the job can be done in Power Query (and returned to an Excel table instead of loading to the data model).

Row-level calculations that involve a related table are generally best done in Power Pivot (otherwise you’d need a flattened table in Power Query with the related column).

“a) Extract filtered data using Power Query or Use Power Pivot measures with CALCULATE Filters”

It depends on the situation. If the data is designed to target specific stakeholders, and “Honda” is the only vehicle is the pertinent to those stakeholders, then Power Query could be an acceptable option (but it probably means creating multiple reports for different makes – one for each group of stakeholders). If the filtering is meant to be flexible, then allowing to user to choose the filter in a pivot table/chart would be appropriate. If the situation is such that you need to change the filter occasionally, it’s trivial to change the filter in Power Query, and send a reduced data set to the data model.

“b) Time Intelligence: Year-to-Date, Year-over-Year comparisons etc”

I couldn’t agree with your assessment on this one.

“c) Split Data Rows”

Only way to know how the split works with a large data set is to try it with a large data set, instead of speculating about the perceived performance. It’s worth the effort too, since splitting a column into multiple columns will involve manual effort & additional calculations in Power Pivot.

October 19, 2016 at 8:09 am

HI Avi

Today I was using Power Query to connect to a SSAS Database and pull a table of data with 50,000 results. It was taking 3+ minutes to load on a 32 Bit Office machine.

I then tried the direct approach via Power Pivot and using the MDX wizard. the refresh took 10 seconds!

I would much prefer to use Power Query but that sort of time improvement is very compelling.

Any thoughts on why the Power Query option would be so slow?

October 25, 2016 at 2:45 pm

Avi,

I attended the PowerPivotPro training this past April in Philly. There were many things I learned in that class, but one nugget of truth really stuck for me and has changed the way I approach Excel reports. During the training you told us that you almost always bring data into your data models now with Power Query… Before the class I was importing data into Power Pivot and building calculated columns to emulate old practices I followed in Excel (formulas, VLOOKUP in particular). I was clinging to the old ways…

Now, after practicing what you taught in the class… I cannot even remember the last time I created a calculated column in Power Pivot. I do everything in Power Query. It is an amazing tool! I also think refreshing is easier now as a result.

I really would like to see you develop a video that demos the value of this approach vs’ the old ways we Excel dudes cling to at times and any best practices you see.

Thanks again Avi! YOU ROCK!

Scott

Power Pivot versus Power Query: Power BI Dilemma

Have Your Cake and Eat It Too – Use Both!

Use Power Query to Get Data

Use Power Pivot to Model Your Data

The Twilight Zone

Sample Scenarios

Conclusion

Leave a Reply to Colin Banfield Cancel reply