Custom function basic syntax in M language (Power Query)

This is about the custom functions in Power Query. 

In an example, we will create a new column (New Price) from current one (Price), adding 1. (Yes, this can be done in Power Query without custom function, but I need very simple example…)

Function written directly in query

Function can be written in a query, which uses it. At the beginning, or whenever before its call.

• let
     MyFunction = (OldPrice as number) =>
        OldPrice + 1,
     Source = Excel.Workbook(File.Contents(“C:\Users\cars.xlsx”), null, true),
     Sheet = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],
     Headers = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
     Types = Table.TransformColumnTypes(Headers,{{“Price”, type number}}),
     Call_Function = Table.AddColumn(Types, “New price”, each MyFunction([Price]), type number)
  in
     Call_Function

Separate function called from query

We need function:

• let
     MyFunction = (OldPrice as number) =>
     OldPrice + 1
  in
     MyFunction

and query calling it:

• let
     Source = Excel.Workbook(File.Contents(“C:\cars.xlsx”), null, true),
     Sheet1_Sheet = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],
     Headers = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
     Types = Table.TransformColumnTypes(Headers,{{“Brand”, type text}, {“Model”, type any}, {“Price”, Int64.Type}, {“Color”, type text}, {“On Stock from:”, type date}}),
     #”Invoked Custom Function” = Table.AddColumn(Types, “New price”, each function([Price]))
  in
     #”Invoked Custom Function”