Power BI Jargons: DAX/M/Power Query

In earlier blogs, we have seen how to analyse data in Power BI with the help of data modeling and visualization techniques. Let us go further in depth of data modeling and how we can tweak the data with the help of different ways which are available in Power BI.

When we hear about Power BI, we hear different jargons like Power Query, DAX , Formula language, M etc. Let us see one by one what these jargons mean and how helpful and important these are while analyzing the data in Power BI.

  • Power Query: is the data extraction and transformation engine. The engine comes with a formula language and a graphical tool. Power Query can connect to various data sources. The graphical interface of Power Query is less powerful than the actual scripting language used behind which is formula language
  • M: is the scripting language behind the Power Query which is actually Power Query Formula language. However since this name is quite lengthy, even Microsoft prefers to call it as M. It is used for data transformation and to be used before you load the data for analysis.
  • DAX: is the Data Analysis eXpression language. It is made for analytical data calculations. Once the data is loaded, DAX can be used to analyse the data in depth. Someone who is knows Excel formulas, would find this familiar.

Both M and DAX serve different purposes such as M is used only in query editor and DAX is used in data modelling. Yes! The same data modelling techniques which we have seen in my earlier posts.

Let us see one example how we can add custom column using both the languages.

Referring to our previous data source of IPL, if we want to add column displaying the runs by which match was won. Let us add this column first using the query editor. As explained in above definition, this would be done before the data is loaded in the Power BI. In Power BI desktop, let us click on Edit query button as shown in below snippet.

Then click on the Add Column Section

There we can see custom column button, which actually asks for the definition of the custom column. Kindly refer to below snippet.

We can give desired name to the column and then write the definition of the column. Here we are adding a column to show by how many runs the match was won, we have to check if win_type is runs, show the win_margin. Referring to below snippet, we can give a name to the column and write the formula. As we type the formula and press ok , it would show if there are any errors. 

 Once all syntax errors are removed, it wil give the message that no syntax errors have been detected, as shown at the bottom of below snippet.

I found the scripting of M similar to old VB code. This way we have created a new column before the data gets loaded in Power BI. 

Once the column is created, lets apply the changes and close the query editor.

Below would be definition of the new column created 

If we look at the advance query editor, our data query would look like as shown in below snippet.Power Query15

As M is step by step language, we can see the “added column” step in the right hand section of the query settings.

Power Query16

Now let us see how to create similar column using DAX. Let us go to Modeling tab and  click on New column as shown in below snippet.

Let us add the definition of the new column and new column will be created. This is very similar to Excel formulas

Now we have same calculated columns created using M -Power Query and DAXDAxandPQ

In this blog , we have seen how we can use Power Query- formula language and DAX to achieve the same results . We can always remember that Power query is the ETL langauge and is step by step language, where as DAX is your analysis language. Now the million dollar question is when to use what?

You can refer below blog to understand the  difference between these two. Hats of to the writer, really liked the way it is explained.


Hope you enjoyed learning these jargons!

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like / share button if you like my article.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI Jargons: DAX/M/Power Query”

Power BI: Licensing Overview

Many times we get the queries if Power BI is a separate license or comes along with Office 365 E1 or Dynamics 365 CE licenses. Hence thought of explaining the same in this blog.

As we discussed in my earlier blog , Power BI can be deployed in models such has on premise, on cloud or hybrid model. With these types of  deployments, we can go with different types of licenses as mentioned below:

  • Power BI Free
  • Power BI Pro
  • Power BI Premium
  • Power BI Report Server
  • Power BI Embedded

Power BI Free: Surprisingly Power BI comes with free licenses with Power BI desktop. This is absolutely free. We can download Power BI desktop for free. Connect with unlimited data sources, no limits on the number of data sources. We can even publish the reports to Power BI service. We can use your organisation account emailID to sign in Power BI service for free and use the same for publishing the reports.

****Only thing which you can not do with Power BI free is sharing the reports securely.****

Sharing of the reports  is only possible by Publishing to the web and which is not secure method. Entire content would be available publicly, others would be able to see the reports, use all functionalities like slicing, drill down etc. Others do not need any Power BI license , but would not be able to edit the reports. This can be done by using embedded code.

Power BI Pro: Power BI Pro comes as 9.00 USD per user license at the time of writing this blog. It has all features what a user can do with in Power BI such as Power BI authoring and sharing. All types of sharing like sharing dashboards, sharing workspaces, sharing reports, Power BI Apps, embed reports in SharePoint etc is available in Pro licensing.

****We just need to keep in mind that to access the shared content using Power BI Pro license, one needs to have the Pro license.****

Pro licensing also provides the integration features as well.

****Embedding  Power BI content in the Custom application, it can not be done in Power BI Pro. ****

Power BI Premium: Power BI premium licensing is considered for large user base for ex equal to or more than 10000. This license type is not based on user, but based on the nodes. These nodes have dedicated capacity and resources and the pricing is based on the nodes which in turn depend on the number of cores. Power BI provides a Power BI calculator to calculate the number of nodes on how much bigger node we require for our needs. This  license provides the facility to publish reports on premise with Power BI report server. It also provides additional features like, huge data storage – 100 TB instead of 10 GB per user, select the deployment model as on premise or cloud, data refresh 48 times per day instead of 8 times per day in Pro etc.

****To access the content shared using Power BI premium license, we do not need the Power BI Pro license****

Power BI Embedded: If we need to embed the Power BI content to custom web application, and need to manage as per user of the custom application, we can go for Power BI embedded license. This license is not based on the users and are actually based on the number of times the content gets rendered. This means that when user interacts with the content such as slices or drills down, the render counter is hit.

Embedded type of licensing is beneficial when users do not need the data continuously and we can predict the usage. The licensing is purely based on number of hits per hour.

****Power BI embedded always have a dependency on web developer for any changes to be done, hence this cost also should be accounted while estimating for Embedded licensing****

Power BI Report Server: SQL Server Enterprise edition and software assurance would give us Power BI report server. If we already have the SQL server Enterprise edition and want to share the reports through on premise report server, we can go with this type of license

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like button if you like my write up.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI: Licensing Overview”