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
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
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 🙂