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"

Leave a Reply

Your email address will not be published.

*

clear formPost comment