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’s Power BI Data Integration team
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 different tools and services:
- Get data and transform in Power BI Desktop
- Power BI dataflows (online)
- Power Platform dataflows (online)
- Get data and transform in Microsoft Excel
- Power Automate
- Azure Data Factory
- SQL Server Integration Services (SSIS)
- SQL Server Analysis Services (SSAS) Tabular
- Azure Analysis Services
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:
File Data Sources
Databases
Power Platform
Azure
Online
Other Sources
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.
Power Query online
Power Query is accessible in desktop tools such as Microsoft Excel and Power BI Desktop. It is also available on online services such as Power BI and Power Platform dataflows and Azure Data Factory. Running the Power Query online is a great way to use Power Query as an ETL service.
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.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Awsome content to start with 🙂