Forum Discussion
List.generate syntax error
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] ) )
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/
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.
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] ) )