Power query custom functions

[dicken]

Hi,
This is just about general advice rather than a specific case, but when writing custom function,
when do I need to use the variables first and then let and when within the the let statement;

l

let
   RunningTotal = (Avlaue, Aindex) =>
   List.Sum(
       List.FirstN( Avlaue, Aindex )
   )
    in RunningTotal

as opposed to somthing like this which pomotes the first two row to headers;

(Rname as table )=>
let
    Custom1 = List.Zip( {Table.ColumnNames(Rname) ,
List.Transform( Table.ToColumns( Table.FirstN(  Rname,2)),(_)=> Text.Combine(_," ")) } ),
    Custom2 = Table.RenameColumns(  Table.RemoveFirstN(Rname,2),Custom1 )
in
    Custom2

I'm looking more for general advice on how to write functions, a lot of videos show how to do a specific thing, so
if anyone knows of a good source of information, please pass it on.

Richard.

 

[jdellasala]

There is no "declaring" variables in M Code. However, a variable must be assigned a value before it's used. While it does matter where the variable is declared in that a variable can be created after a second or subsequent let, but is then only available to that and subsequent let sections as far as I know. At least that's how other languages like it would work.

You can post Power Query M Code clicking the M icon two icons to the right of the XLS icon you used.

 

[dicken]

There is no "declaring" variables in M Code. However, a variable must be assigned a value before it's used. While it does matter where the variable is declared in that a variable can be created after a second or subsequent let, but is then only available to that and subsequent let sections as far as I know. At least that's how other languages like it would work.

You can post Power Query M Code clicking the M icon two icons to the right of the XLS icon you used.

Fair enough, but if you don't use the term variable, what do you use, so in the example I posted;

(Rname as table )=>
let
    Custom1 = List.Zip( {Table.ColumnNames(Rname) ,
List.Transform( Table.ToColumns( Table.FirstN(  Rname,2)),(_)=> Text.Combine(_," ")) } ),
    Custom2 = Table.RenameColumns(  Table.RemoveFirstN(Rname,2),Custom1 )
in
    Custom2

Custom1 is used in Custom2, what do you call it?

RD

 

[JGordon11]

It's a matter of preference. These three custom functions are interchangeable:

(a as number, b as number) as number => a + b

(a as number, b as number) as number=>
let
    Result = a + b
in
    Result

let
    AddItems = (a as number, b as number) as number => a + b
in
    AddItems

For very simple functions like the examples above, I like the short one liner format with no need for the let/in keywords. For more complicated functions I use the second format. and for defining and using a custom function in the same query I use the third format like this:

let
    Source = Table.FromColumns({{1,2,3},{4,5,6}}),
    AddItems = (a as number, b as number) as number => a + b,
    Result = Table.AddColumn(Source, "Total", each AddItems([Column1], [Column2]))
in
    Result

 

[jdellasala]

Fair enough, but if you don't use the term variable, what do you use, so in the example I posted;

(Rname as table )=>
let
    Custom1 = List.Zip( {Table.ColumnNames(Rname) ,
List.Transform( Table.ToColumns( Table.FirstN(  Rname,2)),(_)=> Text.Combine(_," ")) } ),
    Custom2 = Table.RenameColumns(  Table.RemoveFirstN(Rname,2),Custom1 )
in
    Custom2

Custom1 is used in Custom2, what do you call it?

RD

In your code Custom1 and Custom2 are the RESULTS of the functions in those lines. Typically those are tables. To create a variable just assign it a value such as

let
    Var1 = "This is Variable 1",
    Var2 = 1234
in
    Var2

This would simply display 1234
If the code was this:

let
    Var1 = "This is Variable 1",
    Var2 = 1234
in
    Var1

it would display
This is Variable 1
Get it?