Forum Discussion
Declare a variable in a step
Hi all,
I have the following code to add the ISO week number based on my column date, which is called DateProduced
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No",
each if
Number.RoundDown((Date.DayOfYear([DateProduced])-(Date.DayOfWeek([DateProduced], Day.Sunday)+1)+10)/7)=0
then
Number.RoundDown((Date.DayOfYear(#date(Date.Year([DateProduced])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([DateProduced])-1,12,31), Day.Sunday)+1)+10)/7)
else if
(Number.RoundDown((Date.DayOfYear([DateProduced])-(Date.DayOfWeek([DateProduced], Day.Sunday)+1)+10)/7)=53
and (Date.DayOfWeek(#date(Date.Year([DateProduced]),12,31), Day.Sunday)+1<4))
then
1
else
Number.RoundDown((Date.DayOfYear([DateProduced])-(Date.DayOfWeek([DateProduced], Day.Sunday)+1)+10)/7)),
I use this step into many queries and I would like to add a variable INSIDE this step to be able to update the column date, in this query my dates are sitting in the column DateProduced, but in other queries they are sitting in different columns names such as OrderDate, Date etc...
Anyone can help ?
Thanks for your time
oh sorry about that the date at the end should be removed:
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No", each let date = [DateProduced] in if Number.RoundDown((Date.DayOfYear(date)-(Date.DayOfWeek(date, Day.Sunday)+1)+10)/7)=0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year(date)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(date)-1,12,31), Day.Sunday)+1)+10)/7) else if etc....in fact you should be able to just add this almost anywhere:
let myVar = SomeCalculation in
8 Replies
you can use let .. in anywhere e.g.
Table.AddColumn(Source, each let sum = [A] + [B], calc = (sum - [A]) * (sum - [B]) in calc)Thanks artemus
However, I am not sure where to put it in my M code above
Does it work if I do like below ?
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No", let date = [DateProduced] in date each if Number.RoundDown((Date.DayOfYear(date)-(Date.DayOfWeek(date, Day.Sunday)+1)+10)/7)=0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year(date)-1,12,31))-(Date.DayOfWeek(#date(Date.Year(date)-1,12,31), Day.Sunday)+1)+10)/7) else if etc....The problem with your example is that you have the each after the let. Any code before each doesn't have access to the current row. In short, a let before the each would make any values you declare the same for every row. If after the each, then it can access the row data.
. You can put the let in anaywhere you would put a number or a string, or similar value.
