When you get data in Power BI you actually use Power Query Component. In this chapter you will learn about What Power Query is, and what are different types of sources that Power Query can connect. Power Query has also great list of transformations that can be applied on the data set as well (which will be covered in next chapter), and the Power Query formula language M can be used for complex and powerful data transformation situations (will be covered in a chapter after).
In this section you will read an introduction to Power Query. You will learn;
- What Power Query is?
- What types of works can be done with Power Query?
- What are requirements to run Power Query?
- What are features of Power Query Premium?
What Is Power Query?
Power Query previously named as Data Explorer. Data Explorer has been released as public preview for the first time at February 2013. Data Explorer then renamed to Power Query at July 2013, and from that time it had lots of enhancement on the product. Power Query is on a regular and frequent update plan by Microsoft team and usually you can see monthly updates on this, here is the latest update notes (released yesterday!) Power Query has been tested a lot during this period and nowadays used in many real world data transformations and BI solutions.
Power Query is a data extraction and transformation engine. The engine comes with a formula language and a graphical tool. The graphical tool has two major setup versions; one embedded in Power BI Desktop tool, and the other one as an Add-In for Excel. The graphical tool has list of transformations that can be applied on a data set, and it also supports different data sources. However the Power Query formula language is much more powerful than the GUI. Actually there are some features in Power Query engine that not yet has been implemented through GUI, but they are available through M (formula language).
Power Query can connects to set of data sources and read data from them. Set of data sources is variable from text files, to web urls, from database systems to some applications. Wide range of data sources is supported. So to respond to one of the very first questions that usually appears when I introduce this product that Can Power Query connect to Oracle? Sure it does! Not only Oracle, but also MySQL, PostgreSQL, DB2, Sybase, and Teradata.
Power Query can apply many transformations to the data set. you can apply simple transformations such as trimming a text value, and applying numeric calculations to complex transformations easily such as pivot and unpivot. Power Query uses a function library for applying transformations, and the function library contains heaps of transformations for every data type such as table, text, record, list, date, number and so on.
Power Query graphical interface is so easy to work with that even business analyst or a power user can work with it, on the other hand Power Query M language is so powerful that can be used for complex real world challenges of data transformations. Power Query can load the result set into an Excel spreadsheet or it can load it into Power Pivot for data modelling. The version of Power Query used in Power BI Desktop load the result set into a Power Pivot model. I will go through details of Power Pivot in future chapters, for now it would be enough to know that Power Pivot is In-Memory tabular data model engine. Here is an screenshot or the Query Editor window
In below you can see a high level diagram of Power Query conceptually:
How to Use Power Query?
Power Query is available in three different setups:
- As an Excel Add-In for Excel 2010 and 2013
- Embedded in Excel 2016
- Embedded in Power BI Desktop
So if you want to install then you have to install one of the options below:
Excel Add-In for Excel 2010 and 2013:
Please note that the link above might change because Power Query updates frequently and new version will be available almost every month. so you can simply Google it as Power Query Excel add-in.
Excel 2016 download link:
At the time of writing this blog post Excel 2016 is in preview stage, so the link is likely to change.
Power BI Desktop:
What You Can Do With Power Query?
Get Data From Wide Range of Sources
With Power Query you can connect to wide range of data sources. SQL Server or DB2 or Oracle…. All of these database are supported as a source. You can even connect to an Analysis Services instance and fetch data from it. You can connect to file data structures such as text files, XML, CSV, and Excel. You can even read list of files in a folder! You can connect to a range of applications such as Facebook, Salesforce, CRM Online and etc and get data from them. You can get data from Azure services such as Azure SQL database, Azure HD Insight, Azure Blob storage and etc. There are many data sources supported for Power Query (and obviously for Power BI), also more data sources will be available in every update of Power Query or Power BI.
Here is an example set of data sources supported in Power Query (Excel version):
File Data Sources
The Power Query version in Power BI Desktop supports some new applications that still is not implemented as Power Query for Excel, you see some of them below:
Apply Transformation In a Development Editor
Power Query look at the data values with data types such as : Table, Record, List, DateTime, Text, Number, Boolean and etc. There are many data transformation functions for any of these data types. You can apply Merge (similar to join) or Append (similar to UNION) to two tables. You can apply text functions such as getting part of a string, trimming it or length of string. You can apply mathematical functions. You can apply datetime functions such as functions for year, Month, day and week. There are two way to apply these transformations;
- From Query Editor: Graphical User Interface
- From M query language: scripting language
Query Editor will give you a great experience of most common transformations through very easy user interface. you can apply most of transformation with matter of few clicks. The Query Editor in Power BI Desktop or Power Query Add-In for Excel has many common transformations listed. You can see some of them in below screenshots:
M is the formula language behind the scenes of Power Query. Everything you do in the Query Editor will be translated to an M script. M contains full list of functions that you can use. So the powerful side of Power Query is actually M. I will go deep into details of M in this book, because you would need it for solving complex challenges. M is a functional language and it has a simple structure. screenshot below shows an M Code. The details of information about M scripting will be covered in next sections.
Load Data into Destination
You can use Power Pivot as the destination for Power Query to load result set into a data model, or you can use simple Excel spreadsheet for loading data. If you use Power BI Desktop the result set of Power Query automatically will be loaded into a model.
What Are Features of Power Query Premium?
This question might sounds weird at first glance, but makes sense when you think about it that all features I mentioned above are available for free! You don’t have to pay anything for it. Getting data from different sources, applying all kind of transformations to it, and loading it into a data model is all free. So now the question makes sense; What are features of Power Query Premium?
Using Data Catalog
Data Catalog is a metadata definition service that you can define data sources from your organizational data stores or from public data stores that you trust. You can define descriptors for the data structure so Power Query can search through the Data Catalog and fetch information based on it.
You can share your Power Query scripts and queries within your organization
Management using of Shared Queries
You can check the usage of queries that you’ve shared
As you see in above most of features for Power Query Premium is related to Office 365 usage for sharing or Power BI and Azure for data catalog and structure. Most of the features in Power Query (Essential features I have to say) is available for free!
In summary in this section you’ve learned about What is Power Query and what are components of it, you’ve learned features of Power Query, and now you are probably thinking about usage of it in scenarios and challenges that you might have right now! Good start, in next sections I will go through experience of getting data with Power Query and Power BI Desktop.