XL ★Power Query M formula language

20210218 0918 

 

Ref

[1] "docs.microsoft.com/en-us/powerquery-m/"

- Quick tour of the Power Query M formula language

[2] ★★★★★ "exceloffthegrid.com/power-query-formulas/" - very easy starting 

[3] "docs.microsoft.com/en-us/powerquery-m/quick-tour-of-the-power-query-m-formula-language"

- quick tour

[4] "https://blog.crossjoin.co.uk/2016/05/22/understanding-let-expressions-in-m-for-power-bi-and-power-query/"

 

01 Quick Tour [3]

01.01 문법

let  
   Variablename = expression,  
   #"Variable name" = expression2  
in   
   Variablename  

① let 안에 함수 정의를 쓴다.

② in 안에 반환값을 쓴다.

③ #을 이용해서 공백(space)을 쓸 수 있다.** [4]

From [4] The last thing to point out is that if the names of the variables contain spaces, then those names need to be enclosed in double quotes and have a hash # symbol in front. For example here’s a query that returns the value 21 where all the variables have names that contain spaces:

 

01.02 예제

let Orders = Table.FromRecords({  
    [OrderID = 1, CustomerID = 1, Item = "fishing rod", Price = 100.0],  
    [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],  
    [OrderID = 3, CustomerID = 2, Item = "fishing net", Price = 25.0]}),  
    #"Capitalized Each Word" = Table.TransformColumns(Orders, {"Item", Text.Proper})  
in  
    #"Capitalized Each Word"

▶ 결과

 

02 놀라운 점

Row Context [2]
Unless you do some advanced formula magic, the formulas in Power Query have what is known as “Row Context”.  This means the formula is applied to each row one by one.  You may show column names in the formula, however you are not applying this formula to complete columns, but to each row.