Define M Query and its Use

[Anonymous]

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 

[hugoberry]

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.

[v-ljerr-msft]

@Anonymous

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.

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

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

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

Regards

[sheetalshettiga]

Hello,

 Can I get some complex scenarios in Mquery to work on

[danextian]

Hi @Anonymous, 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.

[Anonymous · Accepted solution]

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

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

[Greg_Deckler]

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