【Power BI ---M语言】M语言基础一

 M 语言学习的的实例      https://bengribaudo.com/blog/tag/power-query-m

Power BI 中的M 语言的官方学习文档：https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference

CSDN 上的相关链接：https://blog.csdn.net/zhongguomao/article/details/54572101

 radacad 上M 语言相关   http://radacad.com/category/m

 这里的M 代表着 “Data Mashup”, 也有人说代表："Data Modeling“. M 是一种函数式语言，并且大小写敏感。

 

今天主要介绍M 语言的语法：

在学习语法前，我们keep  in mind the below sentence:

M is much more powerful than the graphical interface of Power Query

 M语言包含两个语法块： Let 语法块 和In 语法块:

let: 定义所有的变量

in: 输出（Note:Yes, in actually means out! just named as in. everything you put in this block will be the output of your query.)

 在Power BI 中可以测试代码法如下：

步骤一：打开Power BI desktop, 在home 处找到get data-->Other-->blank query.

 步骤二：

行尾标识符

every line needs a comma(,) to finish. except the last line before in.

 变量名称

in case that you have some characters such as space, then you need to put the name inside double quote (“) and put a hashtag at the beginning of it(#). something similar to:

 特殊字符

Variable names can have special character, as you can see below variable has all types of characters in it and still runs good.

 

 转义符

Double quote (“) is escape character. you can use it to define variables with names that has another double quote in it. here is an example:

first double quote (highlighted) above is necessary to be before the second double quote (which is part of the variable name).

一步一步编码

Power Query is a step by step transformation. Every transformation usually happens in a step. While you are writing the code, you can also notice that in the right hand side, you will see every variable forms a step.

in screenshot above, you can see every variable is determined as a step. and if the variable has space in the name, it will show it with spaces in list of applied steps.

The last variable is always specified in the in section.

数据类型

There are different ways of defining every literal in Power Query. For example, if you want to define a date variable, here is how to do it;

for defining all other types of literals, here is the reference table to use:

* for function and type; I’ll write another post later to explain how these types works.

Function Call

M is a functional language, and for doing almost everything you need to call a function for it. functions can be easily called with name of the function and specifying parameters for it.

screenshot above uses Date.Year function which fetch year part of a date. Functions names starts always with capital letters: Date.Year()

Comments

like any programming language, you can put some comments in your code. it can be in two forms;

Single line commentary with double slash (//)

Multi line commentary between slash and starts (/* comments */)

 

A real-world example

Now that you know some basics, let’s look at an existing query in advanced editor mode and understand it.

in screenshot above, you can see all basics mentioned so far:

1. let and in block
2. variable names matching steps applied in the query
3. some variable names with hashtag and double quote: #”var name”
4. end of the line characters: comma
5. calling many functions

There are still many parts of this query that you might not understand. specially when using functions. you need to learn what functions are doing in order to understand the code fully. I have written a blog post, that explains how to use #shared keyword to get documentation of all functions in Power Query.

In next posts, I’ll explain another levels of structures in M.