Power BI offers a lot of power and versatility when it comes to ingesting, transforming, visualising, and deriving insights from your data as a data mashup, visualisation, and analytics tool. Capability BI incorporates DAX, “M,” SQL, MDX, and R among other data languages to offer this much flexibility and power. Because of this, it may be difficult for novice users to determine when and when to employ a certain language to achieve their goals. This page discusses the numerous languages that a non-developer general business user (using Power BI) will run into.
DAX
What
The first language of power bi you will probably knowingly come across and use if you are new to the language of Power BI is Data Analysis Expressions (DAX).
DAX is a collection of operators and functions that can be used in conjunction to create formulae and expressions. DAX is the most straightforward to learn and comprehend for novices because it is quite similar to Excel formulas.
when
DAX is applied following the import of your data into your data model.
When using Power Pivot in Excel, Microsoft SQL Server Analysis Services (SSAS) Tabular models, and Power BI Designer.
Why
Build custom columns and measures into a data model using DAX.
How
Learning to consider context is the only truly “tricky” aspect of DAX.
In other words, the results of the DAX formula or expression you develop may vary depending on the environment in which they are used. This gives DAX a lot of power but also introduces some uncertainty and necessitates a different approach to problem-solving.
Take a straightforward DAX measure, for instance:
| Sales in total = SUM ([Sales]) |
When this measure is plotted in a column chart with the department as the x-axis, the findings will be different than when the same measure is plotted in a line chart with the year as the x-axis.
Difficulty
1 – Since DAX formulas are very similar to Excel formulas, it is the simplest language for beginners to learn.
What Language of Power BI Query Formulas “M”
What
The Power Query Formula Language also referred to as “M,” is a potent mashup querying language designed specifically for creating data-mashing queries. It is a functional language with case sensitivities, akin to F#. Although it is doubtful that they are conscious of it, M will most likely be the first language that new users really employ. The explanation is because the queries are most likely using M in the background when users are importing data into their data model, which is typically the initial step in using Power BI Designer. The Query Editor, on the other hand, offers a strong graphical user interface that enables users to do intricate data mashups without ever having to view the M code that the Query Editor generates.
When
When you import your data, M is used.
Where M can be used in the Query Editor of Power BI Designer as well as with Power Query in Excel. Use the Advanced Editor in the Query Editor in Power BI Designer to view the M code that is hidden behind a query. Most likely M code is presented in the Advanced Editor. The only exception is if you build a query using R.
Why
Use M to clean up and alter your data while also importing custom columns.
How M contains a standard library, which is a collection of function definitions that are used frequently. In Power Query’s Query Editor, M functions can be evaluated using the formula bar or relational databases.
Here are a few M function examples:
| Function = NumberText that is IsEven(3) false.Hello, PositionOf(“ll”) 2 |
Difficulty 3
Unless you already know F#, which is unlikely given the popularity of the language (see graph), you will probably find M to be a fairly difficult language to learn. When using M instead of more conventional languages like Java, C, etc., you simply have to think a little differently because it doesn’t “like” a lot of other languages. In my programming experience, I’ve found it to be most comparable to LISP, which similarly places a strong emphasis on sets and lists.
SQL
What
The SQL language is a fairly established and common special-purpose programming language used to manage and manipulate data within databases, regardless of flavor (T-SQL, PL-SQL, etc.).
When
SQL is employed prior to your data import.
Where
In general, relational databases use SQL extensively, and any relational database system can use it with certain dialect changes. When using a data source connector for a relational database like SQL Server, MySQL, PostgreSQL, or IBM DB2, you can use SQL in Power BI. You can paste SQL code into an optional field by selecting “Advanced Options” from the connector interface.
Why
Before you begin working with your data, use SQL to pre-filter, join, or alter it. A complex join or column selection may usually be carried out more quickly using SQL than in M or the data model.
How
With Power BI, one will typically use SQL Queries as opposed to SQL Statements. Unlike statements, which typically have a lasting impact on the data and structure, queries retrieve data based on a specified set of criteria. When using an EXEC function against a saved parameter, this rule is an exception. A somewhat straightforward SQL query looks something like this:
| SELECT row1, row2 FROM table 1 WHERE row3=’Some value’; |
If the value for those rows in “column3” equaled “Some value,” this would return all the columns for “column1” and “column2” for all rows in table “table1”.
Difficulty 2
Because SQL is widely used and has a lengthy history, many people are familiar with it.
MDX
What
Microsoft has established MDX as the standard language for querying multidimensional cubes, including those made using Microsoft SQL Server Analysis Services. Although MDX could at first glance resemble SQL, it is actually a totally separate language. While MDX was created to query OLAP cubes, which include data organised in terms of measures, dimensions, hierarchies, and levels, SQL was created to query relational databases made up of tables where data is organised in terms of columns and rows.
When
MDX is applied prior to data import.
Where
MDX is utilised in Power BI Designer, Excel, and SQL Server Analysis Services (SSAS) queries when connecting to multidimensional cubes.
When using a data source connector for a multidimensional cube like SSAS, you can use MDX in Power BI Designer. In the connector interface, select “Advanced Options,” and an optional field will appear into which you may put the MDX code. This is similar to how to utilise SQL as described previously.
Why
Before you begin working with the data, use SQL to pre-filter and join it. When compared to M or the data model, MDX is typically more effective for performing complex joins or selecting columns.
Difficulty 3
MDX is more challenging to understand than SQL and is kind of a special language in terms of languages.
R
What
R is more of a full-fledged, traditional programming language, in contrast to the other languages discussed here, which are more particular purpose-oriented. R has historically only been used by a small number of data scientists, analysts, and statisticians to do complex statistical computations and data visualisations. R is now incorporated into several Microsoft data platform components, including SQL Server 2016 and Power BI, and has recently gained significantly more popularity. Before, during, and after you import data into your data model are appropriate times to use R.
Where
R has its own runtime and environment for coding because it is a full-fledged programming language. Therefore, before even thinking about importing data into your data model, you can use R to prepare the data. Since full queries or steps within queries may be written in R, it can also be used during data import. R can be utilised in R visualisations when the data has been imported into your model. Choose Get Data | More | Other | R Script in Power BI to utilise R as the foundation for a full query. The next-to-last icon on the right of the “Run R Script” icon in the Power BI Query Editor’s Transform tab will add a R script as a step to your query.
The “R script visual” icon may be found in the second-to-last row of icons when on the Report tab in Power BI. Perhaps not unexpectedly, a capital “R” is used as its icon.
Why
Power BI gains a vast amount of possibilities thanks to the numerous R language modules. The core set of visuals in Power BI is expanded by hundreds thanks to R’s visualisations library. There are countless applications for R.
Difficulty
R receives a high score here thanks in large part to the fact that it is a fully-fledged programming language as well as the size of the language when all available custom modules are taken into account.
Python
In the initial draft of this article, it was predicted that Power BI would soon include Python functionality. This has happened.
What
Python, in contrast, is more of a full-fledged, traditional programming language, whereas the other languages listed below are more specific-purpose programming languages of Power BI. Data scientists and data wranglers frequently employ the full-featured, general-purpose programming language Python. Python routinely ranks among the top 5 programming languages each year.
Before, during, and after you import data into your data model, Python can be employed.
Where
Python has a runtime and coding environment of its own because it is a fully complete programming language.
So, before you even think about importing data into your data model, you may use Python to prepare the data. Python may be used to write full queries or sections inside queries, therefore it can also be used to import data. Python can be applied to Python visualisations once the data has been imported into your model. Choose Get Data | More | Other | Python Script in Power BI to use Python as the foundation for your entire query. The last icon to the right, “Run Python Script,” in the Power BI Query Editor’s Transform tab, will insert a Python script as a step into your query. The “Python script visual” icon may be found in the second-to-last row of icons when on the Report tab in Power BI. It’s not entirely surprising that “Py” is its icon.
Why
Power BI gains a significant number of new capabilities thanks to the numerous Python modules. The core set of graphics in Power BI is expanded by hundreds with the help of the Python visualisations package. Python has a plethora of applications.
Difficulty 4
Python receives a high rating in part because it is a fully-fledged programming language of power bi and due to the size of the language when all available custom modules are taken into account.