Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Greg_Deckler : Thanks for your Blog , nicely explained .much appreciated.
@v-ljerr-msft : Thank for the reply to error part.
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.
@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
Hello,
Can I get some complex scenarios in Mquery to work on
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.
Proud to be a Super User!
@Greg_Deckler : Thanks for your Blog , nicely explained .much appreciated.
@v-ljerr-msft : Thank for the reply to error part.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |