cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oldhasbeen Member
Member

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

Accepted Solutions
Highlighted
Super User
Super User

Re: Why has Microsoft created 3 new languages for Power BI?

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
Highlighted
Super User
Super User

Re: Why has Microsoft created 3 new languages for Power BI?

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

oldhasbeen Member
Member

Re: Why has Microsoft created 3 new languages for Power BI?

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.

 

 

101Mathew Regular Visitor
Regular Visitor

Re: Why has Microsoft created 3 new languages for Power BI?

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

oldhasbeen Member
Member

Re: Why has Microsoft created 3 new languages for Power BI?

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

 

101Mathew Regular Visitor
Regular Visitor

Re: Why has Microsoft created 3 new languages for Power BI?

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

oldhasbeen Member
Member

Re: Why has Microsoft created 3 new languages for Power BI?

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 228 members 2,287 guests
Please welcome our newest community members: