Forum Discussion
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!
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-bi-desktop-part-1/
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] ) )
14 Replies
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-bi-desktop-part-1/
It worked in my sample, so there must be something else in your code.
Please send your full query code for further investigation.
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.
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
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
MiraHi 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] ))I am afraid, if I replace "1" with "B" the list skips progressively .... oouch!
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)..
Many thanks for your time
MiraThe 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)
