Power Query Archives - Dibyendu Deb

Power Query in Power BI plays the role of a data connection technology. It does the data mashup i.e. connect, combine and refine data from many sources to meet the need of our data analysis.

Power Query is available in Excel 2016 or later version of Excel. It can also be added in Excel 2010 as an add-in. It is mainly used for data Extraction-Transformation and Load (ETL) in Excel worksheet or Power BI model.

ETL is something which takes the major portion of time of a data analyst. To ease this task Power Query takes raw data from the source and convert to something more workable form. This form of data is easy to analyze and to draw insights.

Data sources for Power Query

Power Query in Power BI and Excel allows us to extract data from almost any external sources and Excel itself. Here are some examples of the external sources we can bring data from. And there are many more…

Some examples of external sources power query in Power BI can bring data from

After the data has been extracted from the desired source, Power Query helps us clean and prepare the data.

Using Power Query, we can easily append or stack different data tables. We can create relationships by merging different data tables, group and summarize using Pivot feature provided by Power Query.

The beauty of Power Query in Power BI lies in the fact that all this data transformation does not affect the original data set. The data transformation happens in the Power BI memory and we can anytime get back our old data just by removing any particular data transformation step.

Applied Steps can be managed from Query Settings

Once we have summarized the data extracted from diverse sources, the report can be refreshed with one click. Every time new data added in the source data folder, Power Query helps us to update the report accordingly with this refresh feature.

Flow of data processing by Power Query in Power BI

The M language and structure of Power Query

The M language is at the core of Power Query. It is the same as the F# language, case sensitive and contains code blocks starting with "let" and "in" as shown below.

let
     <em> variable </em> = <em> expression </em> [,....]
in
     <em> variable </em>

These blocks consists prcocedural steps of declaring and defining variables. Power Query is very flexible with physical position of these logical steps. That means we can declare a variable at the begining of coding and then can define at the last.

But such a type of coding with a different logical and physical structure is very tough to debug. So, unless absolutely necessary, we should maintain the same logical and physical structure of Power Query.

Editing the Power Query

Luckily we don’t need to write the Power Query in Power BI from scratch. It is already written in the background when we perform the data transformation steps. If it is needed we can tweak the Power Query to make desired changes.

First of all, we need to open the data transformation window by clicking the “Transform data” option in Power BI. Then the Power Query can be edited using either the “Advanced Editor” or editing the code for each “Applied Steps” of “Query Settings“.

The image below consists of an example of Power Query where the data is stored in a variable called “source“. Some other variables are also declared here to store the data with different transformation steps.

The variables can be of any supported type with a unique name. Only if the variable name contains spaces, then the variable must contain a hashtag in the beginning and enclosed with quotes. It is the protocol of declaring Power Query variables.

This article presents a discussion on Power query Vs DAX. As they can perform many similar tasks so it is very normal to get confused when to use which one. Except for some features in common these two are completely different in use and syntax.

Power Query performs the query from the source, through ETL process format and store the physical data tables in Excel or Power BI. DAX comes into the picture once the data is already queried from the source to calculate tables and different analysis.

Here we will briefly go through the concepts of these two with uses and example.

Power Query

It is a powerful query language and helps us build queries to mashup data. The name “M” of the M language which is actually behind the Power Query has come from “Mashup”. Power Query is available with Microsoft Excel 2016 Get & Transform and Power Query in Excel.

It is the M language syntax used in both Excel and Power BI Desktop. Mainly used for data Extraction, Transform and Load (ETL). ETL is an important step and allows us to start with our analysis tasks.

Power Query is used in both Excel worksheets as well as Power BI models.
It has lots of similarities in syntax with F# multi paradigm language which encompasses imperative, functional and object oriented programming methods.
Case sensitive and contains programming blocks with “Let" & “In“
The user may need a little programming experience in order to create advanced data mashup queries.

Power Query is used for query-time transformations in order to shape the data while extracting it.

Uses of Power Query

As mentioned, Power Query is basically for data extraction from the source. So, if we need any kind of data transformation operation to perform, then we will do it using Power Query before loading it into Power BI.

Particularly in Power BI desktop, if we are clicking “Transform” instead of “Load” we are using Power Query or M to make a required transformation in the source data.

In the above image, we have an example of Power Query, used for calculating the length of the target text.

Suppose the source data has two columns with First Name and Last Name. And we want to concatenate them to create one single name column, then we should use M or Power Query to do that.

It can also be done using DAX but it will require several lines of codes. So better to use M for creating a new column, Pivot or Unpivot in Excel etc during ETL itself.

DAX (Data Analysis Expressions)

DAX has few functions identical to Excel but many other functions too and way powerful than Excel functions. It is used for Power Pivot, summarize, slice and dice complex data. Unlike Power Query, DAX performs In-Memory transformation to analyze the extracted data.

DAX is a common language which can be found in SQL server analysis services Tabular, in Power BI and Power Pivot in Excel.

Use of DAX

As the name suggests, DAX is typically for data analysis tasks. After the data is already extracted, it is used for data modelling and reporting. Data analysis, slice and dice can be easily done with DAX.

It is very similar to Excel functions, does not have any programming blocks like M. So, any person has experience in Excel, can easily use DAX.

While creating Power BI report, DAX helps us to calculate Year to Date, calculate aggregates, ranges and several other analytical tasks easily with built in functions.

Here is an example of DAX in the above image. It is used here to create a new column for reporting. The new column calculated the total value of another column. Here you can notice the difference between the syntax of Power Query and DAX.

Final words on Power Query Vs DAX

Power Query and DAX are different. They are built with different purposes, they have different syntax and used in different stages.

Power Query is available both in Excel and Power BI. Users with Excel and the above version has this feature. Whereas DAX is exclusively available with Power BI.

The Data Analysis eXpression language is the far more advanced version of Excel worksheet functions. Still, DAX has many similarities with Excel functions hence easy to use as almost every one of us have experience of using Excel.

Both of them are required to use the Power BI platform to its full potential. You can not chose any one of them as they are specialized for different stages of a complete analytical tasks.

An overview of Power Query in Power BI