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

Define M Query and its Use

Hi All,

 

Request you to please explain M Query.

 

1) Why / When / How / Where  to Use M Query in Power BI Desktop.

2) Please mention couple of scenarios where we should use M Query.

 

I have seen there are lot many function in this URL :- https://msdn.microsoft.com/en-us/library/mt211003.aspx

Just saw Error Handling function in the above link. 

 

Supposingly , I have a Numeric Column in a Table in Query Editor.

When i refresh data , i see there are few records which shows Error in the same column , becasue there are some special character inserted in the original value in the Database. Can we handle such error with M Query (If yes, please explain the process)

 

Thanks a lot 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
mridul Member
Member

Re: Define M Query and its Use

@Greg_Deckler : Thanks for your Blog , nicely explained .much appreciated.

@v-ljerr-msft : Thank for the reply to error part. 

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: Define M Query and its Use

You are almost always using M Query when you import data into Power BI Desktop. The only exception is if you create an R-based query.

 

My recent blog article should answer most of your questions: http://community.powerbi.com/t5/Community-Blog/The-Languages-of-Power-BI/ba-p/69104

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-ljerr-msft
Microsoft

Re: Define M Query and its Use

@mridul

 

As smoupre has mentioned above, M Query is a mashup query language used to query a multitude of data sources. It contains commands to transform data and can return the results of the query and transformations to Power BI data model. So normally we use M Query to query data sources, clean and load data, then use DAX to analyze the data in Power BI.


Supposingly , I have a Numeric Column in a Table in Query Editor.

When i refresh data , i see there are few records which shows Error in the same column , because there are some special character inserted in the original value in the Database. Can we handle such error with M Query (If yes, please explain the process) 


In this scenario, we need to change the data type of that column to Text first, and then create a new custom column where we first check if the value can be converted to a number and otherwise return a default numeric value. See my sample below.

 

Assume we have a table called MyTestTable which has few records that shows Error in the "Value" column like below.

error.PNG

1. Change the data type of "Value" column to Text.

changetype.PNG

2. Use the formula below to create a new custom column to do the error handling.

=try Number.From([IntVal]) otherwise 0

addCol.PNG

o.PNG

Regards

Highlighted
mridul Member
Member

Re: Define M Query and its Use

@Greg_Deckler : Thanks for your Blog , nicely explained .much appreciated.

@v-ljerr-msft : Thank for the reply to error part. 

View solution in original post

hugoberry Member
Member

Re: Define M Query and its Use

We use it on the project to read data from file types that are not yet supported by Power BI Desktop.

M bitwise functions have proven to work pretty well for this kind of scenarios.

Super User I
Super User I

Re: Define M Query and its Use

Hi @mridul, sorry for reviving this post but I'd like to know why, in your formula, you referenced to the same column you were about  to add.


Proud to be a Datanaut!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors