Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
p45cal
Super User
Super User

The 'right' way to bring a custom function requiring arguments 'inline'

I have a table to which I want to add a column listing dates 1 month apart for the duration:

p45cal_0-1737220042201.png

So for the second row I want to generate:

p45cal_1-1737220156068.png

 

Note that this question is not asking about alternate ways to create such a list but a general one about bringing custom functions in-line.

So for this exercise I chose List.Generate. Here's the initial full code including data which doesn't work because, although it produces a new column with lists, when you drill down the list is an error 'We cannot apply field access to the type Date'.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcfBDQAgCASwXe5NwglidBbC/msosb9mgkO51GgOgb9EZ6IkMbby/AroSusEqi4=", BinaryEncoding.Base64), Compression.Deflate)),{"Start Date","End Date"}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"),
    Custom1 = Table.AddColumn(ChangedType, "ListMonths", each List.Generate(()=>[Start Date], each _ <= [End Date], each Date.AddMonths(_, 1)))
in
    Custom1

 

So I created a separate custom function which took 2 arguments (Start and End dates), invoked it, checked it worked, then started whittling it down so that it only needed one argument (End Date) passed to it and brought it in-line the only way I know how, resulting in:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcfBDQAgCASwXe5NwglidBbC/msosb9mgkO51GgOgb9EZ6IkMbby/AroSusEqi4=", BinaryEncoding.Base64), Compression.Deflate)),{"Start Date","End Date"}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"),
    Custom1 = Table.AddColumn(ChangedType, "ListMonths", each ((ed)=>List.Generate(()=>[Start Date], each _ <= ed, each Date.AddMonths(_, 1)))([End Date]))
in
    Custom1

 

which gives the right result:

p45cal_2-1737221182930.png

You can see that I brought the function in-line by enclosing it in parentheses and passing the argument(s) in parentheses straight after:

= Table.AddColumn(ChangedType, "ListMonths", each ((ed)=>List.Generate(()=>[Start Date], each _ <= ed, each Date.AddMonths(_, 1)))([End Date]))

 

So, is this the 'right' way to bring a custom function with arguments in-line, and is there a 'slicker' way?

1 ACCEPTED SOLUTION

@p45cal , regarding "in-line" function definition and call: consider Function.Invoke as an alternative. Example: 

 

( ) => 5 // this is our function
( ( ) => 5 )( ) // this is how you call it now
Function.Invoke( ( ) => 5, { } ) // alternative way to call our function

 

I am not aware of any other alternatives. And there is nothing wrong with your current approach. But the fact that you apply it to solve that particular problem (the one with different "_" conflicting each other) does not look like a ... reasonable one in that particular case. The problem is solved by different identifiers, not by "in-line function call".

 

View solution in original post

4 REPLIES 4
p45cal
Super User
Super User

Thankyou both @AlienSx and @ZhangKun . I wanted to reply before too much time went by (I'm still exploring your suggestions, reading Ben Gribaudo's Primer (I had already been reading it but hadn't reached Part 21)).

While you have provided good solutions to the specific example given, I'm not yet sure if this will answer my query regarding how to bring 'in-line' a custom function which requires arguments; I'm thinking any custom function, any arguments, not necessarily from the same row/record, perhaps even hard-coded arguments in some cases.

Up until now I've been doing it by enclosing the function in parentheses and passing the argument(s) in parentheses straight after, and I was wondering whether there was a better way.

Maybe after a bit more exploring I'll find that you have in fact answered my question but I didn't want this thread to remain unacknowledged for too long!

@p45cal , regarding "in-line" function definition and call: consider Function.Invoke as an alternative. Example: 

 

( ) => 5 // this is our function
( ( ) => 5 )( ) // this is how you call it now
Function.Invoke( ( ) => 5, { } ) // alternative way to call our function

 

I am not aware of any other alternatives. And there is nothing wrong with your current approach. But the fact that you apply it to solve that particular problem (the one with different "_" conflicting each other) does not look like a ... reasonable one in that particular case. The problem is solved by different identifiers, not by "in-line function call".

 

ZhangKun
Super User
Super User

The each keyword is a syntax replacement for "(_)=>". Also, when accessing a field without an identifier, it will default to referencing the variable named "_" in the environment (or call it an identifier).

So, in fact, it is like this:

Table.AddColumn(ChangedType, "ListMonths", 
    (_) => List.Generate( 
        () => _[Start Date], 
        (_) => _ <= _[End Date], 
        (_) => Date.AddMonths(_, 1)
    )
)

The "startdate" field cannot find a variable named "_" in the function (() => _[Start Date]), so it will jump out of the List.Generate function, so it can access the row information in the original table normally. However, "enddate" cannot. Since there is a variable named "_" in the function definition ((_) => _ <= _[End Date]), so it will use the "_" here. It will not jump out of the List.Generate function.

Similar problems also exist in other programming languages, which usually follow the "proximity principle", that is, looking for variables in the current environment (or scope), and if not found, go to the outer layer until the root environment.

AlienSx has already given the answer, but it should be noted that [startdate] is not ambiguous, so it can be modified or not, but [enddate] must be modified.

Here is another way to modify it:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcfBDQAgCASwXe5NwglidBbC/msosb9mgkO51GgOgb9EZ6IkMbby/AroSusEqi4=", BinaryEncoding.Base64), Compression.Deflate)),{"Start Date","End Date"}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"),
    Custom1 = Table.AddColumn(ChangedType, "ListMonths", each 
    List.Generate(
        ()=>[Start Date], 
        // Only modify this line
        (x) => x <= [End Date], 
        each Date.AddMonths(_, 1))
    )
in
    Custom1

 

AlienSx
Super User
Super User

@p45cal the problem with your 1st code is with a "context" you are referring to in List.Generate. Try to replace "each" with (argument_name) => like I did it here: 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcfBDQAgCASwXe5NwglidBbC/msosb9mgkO51GgOgb9EZ6IkMbby/AroSusEqi4=", BinaryEncoding.Base64), Compression.Deflate)),{"Start Date","End Date"}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}},"en-GB"),
    Custom1 = Table.AddColumn(
        ChangedType, 
        "ListMonths", 
        (row) => List.Generate(
            () => row[Start Date], 
            (x) => x <= row[End Date], 
            (x) => Date.AddMonths(x, 1)
        )
    )
in
    Custom1

 

Now it's clear that Start Date and End Date belong to ChangeType table, not List.Generate function. At the same time I replaced "each" in List.Generate with (x) =>. This function works now because List.Generate knows that "row" belongs to the current row (record) of ChangedType table. I would recommend you to forget about "each" for some time. Use (x) => , (y) => , (w) => - whatever you like to use but not each (aka (_) =>)

p.s. read this article written by Ben Gribaudo about identifier scope

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors