THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Power Query now imports relationships #powerquery #powerpivot

The December release of Power Query preview (version 2.9.3502.261) introduced an interesting feature: when you import multiple tables in the data model, relationships existing between tables in the data source are automatically detected and added to the data model as well. This is similar to the behavior you have in Power Pivot, but there is an important difference. When you import one or more tables in a data model where you already imported other tables before, the relationships are detected also between tables already imported and new tables selected for import. As you might know, Power Pivot detects relationships only between tables imported at once, not between tables imported and other tables already in the data model, even when they comes from the same data source.

The detection in Power Query doesn’t work between tables imported in data model directly by Power Pivot. It only works between tables that have been imported using Power Query.

I didn’t test the performance (I can imagine there is some price to pay using Power Query instead of a direct connection), but it’s clear that in the long term Power Query should be THE tool to import data in Power Pivot, or better in Excel, removing the overriding with existing functionalities that do similar things. I really like the progress that Power Query is doing, and I hope that the best is yet to come…

Published Tuesday, December 17, 2013 3:42 PM by Marco Russo (SQLBI)



Henri said:

Isn't there a limitation in row count when using Power Query compared to direct data import to Power Pivot? It is not going to be THE tool if you will have to stick with the million rows limitation.

December 17, 2013 10:41 AM

Marco Russo (SQLBI) said:

The million rows limitation exists only if you import data into an Excel table. If you load the data directly into the data model (and not in an Excel table) then you don't have such a limitation.

December 17, 2013 11:41 AM

David Hager said:

"The detection in Power Query doesn’t work between tables imported in data model directly by Power Pivot. It only works between tables that have been imported using Power Query."

Hopefully, they will be one and the same in the near future.

December 17, 2013 3:32 PM

Marco Russo (SQLBI) said:

I hope so!

December 17, 2013 3:43 PM

R.Jones said:

Hello Marco

I’d like to get someone's opinion on whether the set up I have is most efficient or if there are any other recommendations.

I currently have 3 tables.:

1st table is named “stock” and has 1Million rows

2nd table is named “optimization” and has 400K rows

3rd table is named “fill rate” and has 100K rows

The “stock” table is downloaded via sql from our MRP system

The “optimization” table is also downloaded via sql from our MRP system

The “fill rate” table is downloaded from SAP via business warehouse.

Once all three are downloaded, I import all three via text files into Microsoft Access.

I then create a query to join the three tables and bring in the necessary columns and I also create calculated columns .I run the query and make it a new table in Access and I name the table ‘analysis’ in Access

After those steps I connect powerpivot to the table in Access named ‘anaylsis’ and begin to perform my analysis in Powerpivot.

Is this the most efficient method to use? If I were to use SQL Server Express instead of MS Access would it make this process easier and quicker to run? What are your thoughts?

January 5, 2014 10:09 PM

Marco Russo (SQLBI) said:

I don't know what transformations you do in Access, but you might import the three tables directly in PowerPivot creating existing relationship in the data model. You might avoid Access at all, using PowerQuery for transformations if you need them.


January 10, 2014 6:57 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement