Forum Discussion

Einomi's avatar
Einomi
Helper V
3 years ago
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", ...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • artemus's avatar
    artemus
    3 years ago

    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's avatar
Einomi
Helper V
3 years ago

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's avatar
artemus
Microsoft Employee
3 years ago

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.