(2018-Jan-20) It has been a long journey for the Excel data integration tool Power Query from its early version as an external add-in component in 2010 and 2013 Excel to the built-in feature of the Excel 2016.
Power Query for Excel provides a seamless experience for data discovery, data transformation and enrichment for data analysts, BI professionals and other Excel users. With Power Query, you can combine data from multiple data sources and shape it in order to prepare the data for further analysis in tools like Excel and Power Pivot, or visualization in tools like Power View and Power Map.
It only takes to click New Query button of the Data ribbon to start working with Power Query in Excel, and then the whole experience of selecting different data source begins:
Currently, Excel Power Query allows creating 30 different types of data source connections.
Limiting your data discovery experience with the Excel tool, you tend to ask yourself about the existing worksheets and data models constraints that may prevent you to work with larger file datasets or connecting to a wider range of server databases.
Some of the existing limitations of data modeling in Excel 2016 are:
A) Total number of rows and columns on a worksheet:
Power Query for Excel provides a seamless experience for data discovery, data transformation and enrichment for data analysts, BI professionals and other Excel users. With Power Query, you can combine data from multiple data sources and shape it in order to prepare the data for further analysis in tools like Excel and Power Pivot, or visualization in tools like Power View and Power Map.
It only takes to click New Query button of the Data ribbon to start working with Power Query in Excel, and then the whole experience of selecting different data source begins:
Currently, Excel Power Query allows creating 30 different types of data source connections.
Limiting your data discovery experience with the Excel tool, you tend to ask yourself about the existing worksheets and data models constraints that may prevent you to work with larger file datasets or connecting to a wider range of server databases.
Some of the existing limitations of data modeling in Excel 2016 are:
A) Total number of rows and columns on a worksheet:
- 1,048,576 rows by 16,384 columns
B) Maximum limits of memory storage and file size for Data Model workbooks:
- 32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.
- 64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources
Making a further step into the right direction of the data modeling and data discovery experience with Power BI, you just have to say, where is my data, and in a natural way it gives the option but choosing the Get Data path:
Currently, Power BI allows creating 79 different types of data source connections.
Power BI data modeling limitations:
A) Power BI Desktop:
Currently, Power BI allows creating 79 different types of data source connections.
Power BI data modeling limitations:
A) Power BI Desktop:
- there is no restriction on how large a desktop Power BI file can be locally
B) Power BI Service Online (Free and Pro licenses):
- workspace limit is 10 GB
- dataset limit is 1 GB
C) Power BI Service Online (Premium license):
- workspace limit is 100 TB
- dataset limits are (they may change in future):
- P1 SKU < 3 GB
- P2 SKU < 6 GB
- P3 SKU < 10 GB
The exciting thing is that with SQL Server 2017 Analysis Services for tabular data models of 1400 compatibility level and higher, Microsoft has introduced the very same Get Data experience:
And even Data Query editor with M languages capabilities is now available in Visual Studio, isn't this amazing!
What's new in SQL Server 2017 Analysis Services
Currently, Visual Studio Data Tools for SSAS Tabular allows creating 35 different types of data source connections. But even if the Power BI experience has a lot more variety of various data types to connect, SSAS Tabular now has more advantages of migrating a self-service Power BI models into an organization analytics platform.
And from the data size limitations perspective, the sky is the limit, or Azure, to put it correctly 🙂
