Declare a variable in a step

[Einomi]

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

[Einomi]

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....

[artemus]

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. 

[Einomi]

@artemus  I am sorry I am still confused, any chance you could help with writing the code, look like you know very good M code 🙂 😉

[artemus]

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....

[Einomi]

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...

[artemus · Accepted solution]

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

[Einomi]

Hi @artemus it seems to work now, thanks for your time and your help

[artemus]

you can use let .. in anywhere e.g.

Table.AddColumn(Source, each 
let 
   sum = [A] + [B],
   calc = (sum - [A]) * (sum - [B])
in
   calc)