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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ctrip77
Frequent Visitor

List.generate syntax error

Hi, I have a table with 4 columns Date_start, Date_End, Interval.

I add a custom column with the following code:

List.Generate( () =>
[ a = [Date_Start], b = [Date_End] ],
each [a] <= [b], each [ a = Date.AddMonths( [a], 1 ), b = [b] ], each [a] )

 

The function works as expected with hardcoding the value "1" in the addmonths function.

But the thing is I have to introduce the value on the colum "Interval" instead of "1" so when I tried to add the column to the code, syntax is ok, but when I tried to expand the list values I get "error" text.

List.Generate( () =>
[ a = [Date_Start], b = [Date_End] ],
each [a] <= [b], each [ a = Date.AddMonths( [a], [Interval] ), b = [b] ], each [a] )

 

Where is the problem? How can I reference a column inside the list.generate function?

Is this possible?

for info: the column interval has integer values from 1 to 10

 

thanks in advance!

 

2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

Hi @ctrip77 ,

there's no way to make the necessary adjustment in the UI window alone, that pops up when you add a column.

You have to adjust the code that has been generated by your current step like this

 

= Table.AddColumn(#"Changed Type", "Custom", (x) => List.Generate( () =>
[ a = x[Date_Start], b = x[Date_End] ],
each [a] <= [b], 
each [ a = Date.AddMonths( [a], x[Interval] ), b = [b] ], each [a] ))

Reason for this is the syntax sugar generated here: You have nested functions who both produce short code and therefore create ambiguity. Therefore you replace the shortcode of the outer function by a proper function definition: (x) => .... where the x stands for the current record that is passed into the function. Then use the x as the record identifier and you can use the square brackets as lookup operators for the record fields.

 

Check out this series if you want to learn more about M's evaluation context: https://ssbi-blog.de/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power...

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Oh yes, my bad:

 Table.AddColumn(#"Changed Type", "Custom", 
 
(x) =>  if x[interval] = 2 then List.Generate( () =>
[ a = x[Date_Start], b = x[Date_End] ],
each [a] <= [b], 
each [ a = Date.AddMonths( [a], x[Interval] ), b = [b] ], each [a] )
else
 List.Generate( () => [ a = x[Date_Start], b = x[Date_End] ], each [a] <= [b], each [ a = Date.AddYears( [a], x[Interval] ), b = [b] ], each [a] )

) 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

14 REPLIES 14
slorin
Super User
Super User

Hi

if you want a list of year end dates after  #date(2025,7,3)

= {#date(2025,7,3), #date(2025,12,31),  #date(2026,12,31), ... , #date(2024+[Years],12,31)}

 

(x) => 
{Date.AddYears(x[Delivery Date],1)}
&
List.Transform({1..x[Years]}, each Date.AddYears(Date.EndOfYear(x[Delivery Date]),_))

Stéphane

ImkeF
Super User
Super User

Hi @MiraAbel ,
not sure I understand the requirement correctly, but please try replacing the 1 by b like so:
= Table.AddColumn(#"Changed Type", "Cash Flow Years", (x) => List.Generate( () =>
[ a = x[Delivery Date], b=0 ],
each [b] <= x[ Years],
each [b = [b]+1 , a = Date.AddYears( [a], b )], each [a] ))

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

The solution to my question is as follows: 

= Table.AddColumn(#"Changed Types", "Dates", (x) => List.Generate( () =>
[ a = Date.AddYears( x[Delivery Date], 1 ), b=0 ],
each [b] <= x[Years],
each [b = [b]+1 , a = Date.AddYears( [a], 1 )], each [a] ))

It answers a bigger question for me that List.Generate in fact can start from a function (formula!) rather than just a starting value (text, or number etc)

I am afraid, if I replace "1" with "B" the list skips progressively .... oouch!

MiraAbel_0-1697205545904.png


I am trying to get the following list for the record in [Delivery Date]= #date(2024, 3, 31) it should start  from #date(2025, 3, 31)..

MiraAbel_1-1697205685512.png


Many thanks for your time
Mira


MiraAbel
Frequent Visitor

Hello 

I have a similar issue but with a twist... I am after a list of year end dates for each year of economic life.
My table has columns [Delivery Date] and [Years]
I need a list to start from the next year date, e.g. for  [Delivery Date]= #date(2024, 7, 3) it should start  from #date(2025, 7, 3)..


How can I change my M code, as now it starts each list from the [Delivery Date]

= Table.AddColumn(#"Changed Type", "Cash Flow Years", (x) => List.Generate( () =>
[ a = x[Delivery Date], b=0 ],
each [b] <= x[ Years],
each [b = [b]+1 , a = Date.AddYears( [a], 1 )], each [a] ))


Thank you veru much
Mira

ImkeF
Super User
Super User

Hi @ctrip77 ,

there's no way to make the necessary adjustment in the UI window alone, that pops up when you add a column.

You have to adjust the code that has been generated by your current step like this

 

= Table.AddColumn(#"Changed Type", "Custom", (x) => List.Generate( () =>
[ a = x[Date_Start], b = x[Date_End] ],
each [a] <= [b], 
each [ a = Date.AddMonths( [a], x[Interval] ), b = [b] ], each [a] ))

Reason for this is the syntax sugar generated here: You have nested functions who both produce short code and therefore create ambiguity. Therefore you replace the shortcode of the outer function by a proper function definition: (x) => .... where the x stands for the current record that is passed into the function. Then use the x as the record identifier and you can use the square brackets as lookup operators for the record fields.

 

Check out this series if you want to learn more about M's evaluation context: https://ssbi-blog.de/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power...

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ctrip77
Frequent Visitor

Thanks for your reply! @ImkeF  

But Im still having syntax problems:

Expression.Error: Cant convert a value of type Function to type Table.
Details:
Value=Function
Type=Type

 

This is becoming very hard!!!

thanks!

It worked in my sample, so there must be something else in your code. 

Please send your full query code for further investigation.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ctrip77
Frequent Visitor

Thanks  a lot @ImkeF ! it worked, but I now need to tune my code, to insert an "IF" inside, for example if the field "interval" has a value of "1" then use addmonths, if the field "interval" has a value of "2" use addyears.

Something like (I will put my code in Bold)

 

= Table.AddColumn(#"Changed Type", "Custom", 
each IF [interval] = 2 then
(x) =>
List.Generate( () => [ a = x[Date_Start], b = x[Date_End] ], each [a] <= [b], each [ a = Date.AddMonths( [a], x[Interval] ), b = [b] ], each [a] )
else
(x) => List.Generate( () => [ a = x[Date_Start], b = x[Date_End] ], each [a] <= [b], each [ a = Date.AddYears( [a], x[Interval] ), b = [b] ], each [a] )

 

apart from a capitalisation issue, that code is already correct (M is case sensitive):

 

 Table.AddColumn(#"Changed Type", "Custom", 
each if [interval] = 2 then 
(x) => List.Generate( () =>
[ a = x[Date_Start], b = x[Date_End] ],
each [a] <= [b], 
each [ a = Date.AddMonths( [a], x[Interval] ), b = [b] ], each [a] )
else
(x) => List.Generate( () => [ a = x[Date_Start], b = x[Date_End] ], each [a] <= [b], each [ a = Date.AddYears( [a], x[Interval] ), b = [b] ], each [a] )

) 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ctrip77
Frequent Visitor

thanks for your reply @ImkeF ! 

After change the IF to lowercase, Now instead having the text "list" on the column I see "function" and if I click on the cell, in the bottom pane (where before appear the expanded list) now is showing "function (x as any) as any

I think Im missing something but cant find it!

 

thanks in advance!

 

Oh yes, my bad:

 Table.AddColumn(#"Changed Type", "Custom", 
 
(x) =>  if x[interval] = 2 then List.Generate( () =>
[ a = x[Date_Start], b = x[Date_End] ],
each [a] <= [b], 
each [ a = Date.AddMonths( [a], x[Interval] ), b = [b] ], each [a] )
else
 List.Generate( () => [ a = x[Date_Start], b = x[Date_End] ], each [a] <= [b], each [ a = Date.AddYears( [a], x[Interval] ), b = [b] ], each [a] )

) 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ctrip77
Frequent Visitor

Thanks a lot @ImkeF !!!

I think this snippet of code should be posted in a blog of powerquery!, because I search a lot about a case like mine, and didnt find any, some examples found, but not with nested Ifs and use of fields as parameters.

 

thanks again!!!

artemus
Microsoft Employee
Microsoft Employee

Using "each" moves you into the context of the list. So it is trying to access the [Interval] in list element. Try using a single parameter function insteead.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors