History of Power Query in Excel Versions: 2010, 2013, 2016, 2019, 2021, 365, for Mac and for web
Until 2010, there was a clear distinction between small and large data analysis. Excel was useful for tables of up to 1 million data, and for more than that you had to use the famous Crystal Report or similar BI system. Erasing the boundaries between IT support and business users started with PowerPivot, which you can read more about on the pages of this site.
Data was less and less entered into spreadsheets and more and more data was already in databases and various other sources. Business users have more and more data and a growing need to analyze that data. IT support is becoming a bottleneck in business, which has forced Microsoft to offer additions in the form of PowerPivot and PowerQuery with Office 2010 to download, transform and analyze more than 1 million data.
Why do we need Power Query?
Since the worksheet in Excel is limited to 1 million rows (1,048,576 rows), Velocity technology was designed. Create Pivot Tables on data stored in SQL Server or other tabular formats such as XML, JSON, CSV, TXT, MDB, PDF, etc.
Power Query or in translation Powerful queries is a technology that allows you not to copy data into excel and burden the working memory, but only to establish a connection with the data source. Make the necessary adjustments to the format, select the necessary columns from the table, transform the data form in one word, and only then use them to create a pivot (derived) table as a report form.
This data processing process is called the ETL process (Extract Transform Load) and is necessary in every data processing process. The first form of data we receive from a source is not in such a state that it can be immediately analyzed. Microsoft Excel went a step further and supplemented the data preparation process with another set of activities, which is combining data, as you can see in the following illustration.
History of Power Query
In addition to Excel users, there is another category of people who dedicate their professional lives to data analysis: business intelligence (BI) professionals. BI is the science of gaining insights from large amounts of information, and in recent years, BI professionals have learned and created many new techniques and tools to manage systems that can handle volumes of hundreds of millions or even billions of rows. BI systems require the effort of many professionals and expensive hardware to run. They are powerful, but expensive and slow to build, which is a serious drawback.
Linking data
To connect the data, we must select the Data tab.
Importing data using Power Query is easy. Excel provides many common data transfer links that can be accessed from the Data tab and found from the Get Data command.
- Download data from a single file such as an Excel workbook, text or CSV file, XML and JSON files. You can also import multiple files from a given folder.
- Download data from various databases such as SQL Server, Microsoft Access, Analysis Services, SQL Server Analysis Server, Oracle, IBM DB2, MiSQL, PostgreSQL, Sybase, Teradata and SAP HANA databases.
- Download data from Microsoft Azure
- Get data from online services like Sharepoint, Microsoft Exchange, Dynamics 365, Facebook and Salesforce.
- Retrieve data from other sources such as a table or scope within the current workbook, from the web, Microsoft Query, Hadoop, OData feed, ODBC, and OLEDB.
- We can join two queries similar to joining two queries in SQL.
- We can add a query to another query similar to joining two queries in SQL.
Note : Available data connection options will depend on your version of Excel.
There are several common query types available at the top level of the ribbon commands found in the Get & Transform section of the Data tab . From here we can easily access From Text/CSV , From Web and From Table/Range queries. They are just duplicated outside of the Get Data command for ease of use, as you will probably use them more often.
Depending on which type of data connection you choose, Excel will guide you through setting up the connection, and there may be several options to choose from during the process.