cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Why has Microsoft created 3 new languages for Power BI?

H

 

I've been getting into Power BI this year and this is a question I keep asking myself: why has Microsoft created 3 new language for it, i.e. "M" (for use in Query), DAX (for PBI Desktop & PowerPivot) and "R-SCript" ((for Query)?

 

OHB

1 ACCEPTED SOLUTION

Software languages are built to solve problems.

 

Power Query is a transformational tool that needs to deal with (primarily) Tabular data. M is a functional language that is designed to do that task, and do it well. Other languages were not well suited. 

 

The Vertipaq engine inside Power BI is a column store database. This is a brand new DB design that stores data in columns, not rows. They needed a new language to take advantage of the benefits of the new design. They also wanted a functional language so that it would be easy for Excel users to learn. 

 

Microsoft did not create R. It is an industry standard stats language that has its roots from “S” and “s-Plus”

 

if you want to consider the alternative, look no further than Microsoft trying to make Windows the operating system for mobile phones. Someone no doubt said “why do we need a new OS when we already have one?”  One reason Power BI is such a game changing product is because it has no ties to legacy systems. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

6 REPLIES 6

Software languages are built to solve problems.

 

Power Query is a transformational tool that needs to deal with (primarily) Tabular data. M is a functional language that is designed to do that task, and do it well. Other languages were not well suited. 

 

The Vertipaq engine inside Power BI is a column store database. This is a brand new DB design that stores data in columns, not rows. They needed a new language to take advantage of the benefits of the new design. They also wanted a functional language so that it would be easy for Excel users to learn. 

 

Microsoft did not create R. It is an industry standard stats language that has its roots from “S” and “s-Plus”

 

if you want to consider the alternative, look no further than Microsoft trying to make Windows the operating system for mobile phones. Someone no doubt said “why do we need a new OS when we already have one?”  One reason Power BI is such a game changing product is because it has no ties to legacy systems. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Anonymous
Not applicable

Thanks Matt.  Very illuminating.

One other question:  Microsoft developed "M" with

  • Counts using base zero
  • Case sensitivity in both variables and function names

I can't see any advantage iin either of these, they just seem to make coding in M more error-prone - maybe I'm missing something.

 

 

Starting from zero

 

Most langauges, and I think databases start indexing rows based on a starting point of zero.  This is then consitant with people familiar with these.

 

There are some operations when starting from zero is important

 

Case sensitivity is annoying but, it allow us to name something the same but using case as a way to have additional verions.  I'm sure someone else can give a better reasoning here

Anonymous
Not applicable

I see your point, but from my perspective - as an IT trainer - and the students I teach, case sensitivity and counting from zero are just a pain in the bottom.

All the people I teach have a fair level of expertise in Excel and several have said that they find M frustrating, as it's so inconsistent with Excel. Case sensitivity is the most common grouse, functions being case sensitive seems to add no value at all  and yes, you could use case sensitive names to distinguish three different, but related variables, there are better alternatives. Take calculations of product profitability, for example - you can a=often do this several ways so I might have 3 variables called something like ProdProfV1, ProdProfV2 & ProdProfV3, which is an awful lot less confusing than, say, ProdProf, PRODPROF and prodprof.

Counting form zero is great if you're a DBA but it makes functions like "Text.Range"  & "Text.PositionOf" very confusing for the bog-standard Excel user

 

I agree, from an Excel user point of view, this is annoying.  But that is why the GUI allows you to do so much with no coding.

 

M is data handelling programming language, this does require a fundimentally different approach.

 

Could they have done, more, most likely, but given the speed of development required, and the GUI this tool is amazing

Anonymous
Not applicable

That's probably a fair summary, Matt. 

As a trainer, I hear a fair amount of grumbling about Microsoft software, including Power Query/Power BI, but it's important (for both me & my students) not to lose sight of how powerful PQ/PBI are, even if M can be bit of a dog to work with if you need functionality that the UI can't provide. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors