The expertise of Power Query lies in three key areas:
Importing Data from Different Sources
Power Query allows you to retrieve data from sources such as:
- Excel workbooks
- Text files
- Web pages
- Online Services
This is a shortlist of sources that Power Query can natively communicate with, and new data connectors are constantly being released.
You can also work with what is known as “Big Data”; data that exceeds Excel’s native 1 million row limit. If you want specifics, “Big Data” is defined as a maximum of 2 billion tables, with each table having a maximum of 2 billion columns and 2 billion rows.
This works out to a file size of just over 9 quintillion bytes of data. Microsoft considers this “effectively unlimited”. For the VAST majority of users, this is an accurate statement.
Transforming and Cleaning Data
Power Query is equipped with an amazing transformation engine.
(The above is just one of several sets of transformation tools.)
You can shape and change data with just a few clicks of the mouse.
Transformations, like those including complex formulas and possibly VBA code, that would take hours to develop in Excel can be accomplished in seconds in Power Query.
Transformations such as:
- Compare two datasets and identify the differences/similarities
- Split data into separate columns
- Merge separated columns of data into a single column
- Group data and calculate aggregations
- Transpose, pivot, and unpivot data
- Calculate differences between dates or times
- Standardize the formatting of text
The list of transformation possibilities is almost endless.