Forum Discussion
Solved
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",
...
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
From your example, you would move the each up to just before your let statement like:
AddWeekNo = Table.AddColumn(#"Colonnes renommées1", "Week No", each
let
date = [DateProduced]
in
date
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....
Hi artemus
Thanks for your reply.
However, my query editor indicated me that we need a comma like below
let
mydate = [DateProduced]
in
mydate,
etc...
From here there is no more syntax erros, however, in the UI, I got an error saying that PQ cannot find mydate...
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